﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Configuration;
using System.Data;
using CrystalDecisions.CrystalReports.Engine;
using System.IO; // Using to generate spreadsheet
using System.Xml;// Using to generate spreadsheet
using DTO;
using Moved;

namespace BUS
{
    public class LichBUS
    {
        private static String _connString = "";

        static LichBUS()
        {
            _connString = ConfigurationManager.ConnectionStrings["WebChamCongConnectionString"].ConnectionString.ToString();
        }
        public static DataTable XuatThietLapCong(int departID, DateTime time)
        {
            int localDepartmentID = departID;
            DataTable result = null;
            int i = 1;
            double s = 0;
            List<NhanVienDTO> listnv = BUS.NhanVienBUS.GetNhanViensByDepartID(localDepartmentID);
            if (listnv.Count < 1)
            {
                return result;
            }
            result = new DataTable();
            result.Columns.Add("STT");
            result.Columns.Add("FullName");

            result.Columns.Add("Day1");
            result.Columns.Add("Day2");
            result.Columns.Add("Day3");
            result.Columns.Add("Day4");
            result.Columns.Add("Day5");

            result.Columns.Add("Day6");
            result.Columns.Add("Day7");
            result.Columns.Add("Day8");
            result.Columns.Add("Day9");
            result.Columns.Add("Day10");

            result.Columns.Add("Day11");
            result.Columns.Add("Day12");
            result.Columns.Add("Day13");
            result.Columns.Add("Day14");
            result.Columns.Add("Day15");

            result.Columns.Add("Day16");
            result.Columns.Add("Day17");
            result.Columns.Add("Day18");
            result.Columns.Add("Day19");
            result.Columns.Add("Day20");

            result.Columns.Add("Day21");
            result.Columns.Add("Day22");
            result.Columns.Add("Day23");
            result.Columns.Add("Day24");
            result.Columns.Add("Day25");

            result.Columns.Add("Day26");
            result.Columns.Add("Day27");
            result.Columns.Add("Day28");
            result.Columns.Add("Day29");
            result.Columns.Add("Day30");

            result.Columns.Add("Day31");

            result.Columns.Add("SumHour");

            result.Columns.Add("h1");
            result.Columns.Add("h2");
            result.Columns.Add("h3");
            result.Columns.Add("h4");
            result.Columns.Add("h5");

            result.Columns.Add("h6");
            result.Columns.Add("h7");
            result.Columns.Add("h8");
            result.Columns.Add("h9");
            result.Columns.Add("h10");

            result.Columns.Add("h11");
            result.Columns.Add("h12");
            result.Columns.Add("h13");
            result.Columns.Add("h14");
            result.Columns.Add("h15");

            result.Columns.Add("h16");
            result.Columns.Add("h17");
            result.Columns.Add("h18");
            result.Columns.Add("h19");
            result.Columns.Add("h20");

            result.Columns.Add("h21");
            result.Columns.Add("h22");
            result.Columns.Add("h23");
            result.Columns.Add("h24");
            result.Columns.Add("h25");

            result.Columns.Add("h26");
            result.Columns.Add("h27");
            result.Columns.Add("h28");
            result.Columns.Add("h29");
            result.Columns.Add("h30");

            result.Columns.Add("h31");
            foreach (var obj in listnv)
            {
                DataRow itemRow = result.NewRow();
                itemRow["STT"] = i;
                using (Moved_DB dc = new Moved_DB(_connString))
                {
                    var empSchedules = (from ws in dc.WIT_Schedule
                                        join ue in dc.AL_UserInfo on ws.UserID equals ue.UserID
                                        where ue.DepartmentID == obj.DepartmentId &&
                                              ue.UserID == obj.UserId &&
                                              ws.StartTime.Month == time.Month &&
                                              ws.StartTime.Year == time.Year
                                        select new DTO.Schedule
                                        {
                                            UserId = ue.UserID,
                                            FullName = ue.FullName,
                                            Day1 = (int)ws.Day1,
                                            Day2 = (int)ws.Day2,
                                            Day3 = (int)ws.Day3,
                                            Day4 = (int)ws.Day4,
                                            Day5 = (int)ws.Day5,
                                            Day6 = (int)ws.Day6,
                                            Day7 = (int)ws.Day7,
                                            Day8 = (int)ws.Day8,
                                            Day9 = (int)ws.Day9,
                                            Day10 = (int)ws.Day10,
                                            Day11 = (int)ws.Day11,
                                            Day12 = (int)ws.Day12,
                                            Day13 = (int)ws.Day13,
                                            Day14 = (int)ws.Day14,
                                            Day15 = (int)ws.Day15,
                                            Day16 = (int)ws.Day16,
                                            Day17 = (int)ws.Day17,
                                            Day18 = (int)ws.Day18,
                                            Day19 = (int)ws.Day19,
                                            Day20 = (int)ws.Day20,
                                            Day21 = (int)ws.Day21,
                                            Day22 = (int)ws.Day22,
                                            Day23 = (int)ws.Day23,
                                            Day24 = (int)ws.Day24,
                                            Day25 = (int)ws.Day25,
                                            Day26 = (int)ws.Day26,
                                            Day27 = (int)ws.Day27,
                                            Day28 = (int)ws.Day28,
                                            Day29 = (int)ws.Day29,
                                            Day30 = (int)ws.Day30,
                                            Day31 = (int)ws.Day31
                                        }
                                                 );
                    foreach (var objSchedules in empSchedules)
                    {
                        itemRow["FullName"] = objSchedules.FullName;
                        itemRow["Day1"] = GetShiftCodeByID(objSchedules.Day1);
                        itemRow["Day2"] = GetShiftCodeByID(objSchedules.Day2);
                        itemRow["Day3"] = GetShiftCodeByID(objSchedules.Day3);
                        itemRow["Day4"] = GetShiftCodeByID(objSchedules.Day4);
                        itemRow["Day5"] = GetShiftCodeByID(objSchedules.Day5);
                        itemRow["Day6"] = GetShiftCodeByID(objSchedules.Day6);
                        itemRow["Day7"] = GetShiftCodeByID(objSchedules.Day7);
                        itemRow["Day8"] = GetShiftCodeByID(objSchedules.Day8);
                        itemRow["Day9"] = GetShiftCodeByID(objSchedules.Day9);
                        itemRow["Day10"] = GetShiftCodeByID(objSchedules.Day10);
                        itemRow["Day11"] = GetShiftCodeByID(objSchedules.Day11);
                        itemRow["Day12"] = GetShiftCodeByID(objSchedules.Day12);
                        itemRow["Day13"] = GetShiftCodeByID(objSchedules.Day13);
                        itemRow["Day14"] = GetShiftCodeByID(objSchedules.Day14);
                        itemRow["Day15"] = GetShiftCodeByID(objSchedules.Day15);
                        itemRow["Day16"] = GetShiftCodeByID(objSchedules.Day16);
                        itemRow["Day17"] = GetShiftCodeByID(objSchedules.Day17);
                        itemRow["Day18"] = GetShiftCodeByID(objSchedules.Day18);
                        itemRow["Day19"] = GetShiftCodeByID(objSchedules.Day19);
                        itemRow["Day20"] = GetShiftCodeByID(objSchedules.Day20);
                        itemRow["Day21"] = GetShiftCodeByID(objSchedules.Day21);
                        itemRow["Day22"] = GetShiftCodeByID(objSchedules.Day22);
                        itemRow["Day23"] = GetShiftCodeByID(objSchedules.Day23);
                        itemRow["Day24"] = GetShiftCodeByID(objSchedules.Day24);
                        itemRow["Day25"] = GetShiftCodeByID(objSchedules.Day25);
                        itemRow["Day26"] = GetShiftCodeByID(objSchedules.Day26);
                        itemRow["Day27"] = GetShiftCodeByID(objSchedules.Day27);
                        itemRow["Day28"] = GetShiftCodeByID(objSchedules.Day28);
                        itemRow["Day29"] = GetShiftCodeByID(objSchedules.Day29);
                        itemRow["Day30"] = GetShiftCodeByID(objSchedules.Day30);
                        itemRow["Day31"] = GetShiftCodeByID(objSchedules.Day31);
                        itemRow["h1"] = BUS.CaBUS.getHourWork(objSchedules.Day1) / 60;
                        itemRow["h2"] = BUS.CaBUS.getHourWork(objSchedules.Day2) / 60;
                        itemRow["h3"] = BUS.CaBUS.getHourWork(objSchedules.Day3) / 60;
                        itemRow["h4"] = BUS.CaBUS.getHourWork(objSchedules.Day4) / 60;
                        itemRow["h5"] = BUS.CaBUS.getHourWork(objSchedules.Day5) / 60;
                        itemRow["h6"] = BUS.CaBUS.getHourWork(objSchedules.Day6) / 60;
                        itemRow["h7"] = BUS.CaBUS.getHourWork(objSchedules.Day7) / 60;
                        itemRow["h8"] = BUS.CaBUS.getHourWork(objSchedules.Day8) / 60;
                        itemRow["h9"] = BUS.CaBUS.getHourWork(objSchedules.Day9) / 60;
                        itemRow["h10"] = BUS.CaBUS.getHourWork(objSchedules.Day10) / 60;
                        itemRow["h11"] = BUS.CaBUS.getHourWork(objSchedules.Day11) / 60;
                        itemRow["h12"] = BUS.CaBUS.getHourWork(objSchedules.Day12) / 60;
                        itemRow["h13"] = BUS.CaBUS.getHourWork(objSchedules.Day13) / 60;
                        itemRow["h14"] = BUS.CaBUS.getHourWork(objSchedules.Day14) / 60;
                        itemRow["h15"] = BUS.CaBUS.getHourWork(objSchedules.Day15) / 60;
                        itemRow["h16"] = BUS.CaBUS.getHourWork(objSchedules.Day16) / 60;
                        itemRow["h17"] = BUS.CaBUS.getHourWork(objSchedules.Day17) / 60;
                        itemRow["h18"] = BUS.CaBUS.getHourWork(objSchedules.Day18) / 60;
                        itemRow["h19"] = BUS.CaBUS.getHourWork(objSchedules.Day19) / 60;
                        itemRow["h20"] = BUS.CaBUS.getHourWork(objSchedules.Day20) / 60;
                        itemRow["h21"] = BUS.CaBUS.getHourWork(objSchedules.Day21) / 60;
                        itemRow["h22"] = BUS.CaBUS.getHourWork(objSchedules.Day22) / 60;
                        itemRow["h23"] = BUS.CaBUS.getHourWork(objSchedules.Day23) / 60;
                        itemRow["h24"] = BUS.CaBUS.getHourWork(objSchedules.Day24) / 60;
                        itemRow["h25"] = BUS.CaBUS.getHourWork(objSchedules.Day25) / 60;
                        itemRow["h26"] = BUS.CaBUS.getHourWork(objSchedules.Day26) / 60;
                        itemRow["h27"] = BUS.CaBUS.getHourWork(objSchedules.Day27) / 60;
                        itemRow["h28"] = BUS.CaBUS.getHourWork(objSchedules.Day28) / 60;
                        itemRow["h29"] = BUS.CaBUS.getHourWork(objSchedules.Day29) / 60;
                        itemRow["h30"] = BUS.CaBUS.getHourWork(objSchedules.Day30) / 60;
                        itemRow["h31"] = BUS.CaBUS.getHourWork(objSchedules.Day31) / 60;
                        s += BUS.CaBUS.getHourWork(objSchedules.Day1);
                        s += BUS.CaBUS.getHourWork(objSchedules.Day2);
                        s += BUS.CaBUS.getHourWork(objSchedules.Day3);
                        s += BUS.CaBUS.getHourWork(objSchedules.Day4);
                        s += BUS.CaBUS.getHourWork(objSchedules.Day5);
                        s += BUS.CaBUS.getHourWork(objSchedules.Day6);
                        s += BUS.CaBUS.getHourWork(objSchedules.Day7);
                        s += BUS.CaBUS.getHourWork(objSchedules.Day8);
                        s += BUS.CaBUS.getHourWork(objSchedules.Day9);
                        s += BUS.CaBUS.getHourWork(objSchedules.Day10);
                        s += BUS.CaBUS.getHourWork(objSchedules.Day11);
                        s += BUS.CaBUS.getHourWork(objSchedules.Day12);
                        s += BUS.CaBUS.getHourWork(objSchedules.Day13);
                        s += BUS.CaBUS.getHourWork(objSchedules.Day14);
                        s += BUS.CaBUS.getHourWork(objSchedules.Day15);
                        s += BUS.CaBUS.getHourWork(objSchedules.Day16);
                        s += BUS.CaBUS.getHourWork(objSchedules.Day17);
                        s += BUS.CaBUS.getHourWork(objSchedules.Day18);
                        s += BUS.CaBUS.getHourWork(objSchedules.Day19);
                        s += BUS.CaBUS.getHourWork(objSchedules.Day20);
                        s += BUS.CaBUS.getHourWork(objSchedules.Day21);
                        s += BUS.CaBUS.getHourWork(objSchedules.Day22);
                        s += BUS.CaBUS.getHourWork(objSchedules.Day23);
                        s += BUS.CaBUS.getHourWork(objSchedules.Day24);
                        s += BUS.CaBUS.getHourWork(objSchedules.Day25);
                        s += BUS.CaBUS.getHourWork(objSchedules.Day26);
                        s += BUS.CaBUS.getHourWork(objSchedules.Day27);
                        s += BUS.CaBUS.getHourWork(objSchedules.Day28);
                        s += BUS.CaBUS.getHourWork(objSchedules.Day29);
                        s += BUS.CaBUS.getHourWork(objSchedules.Day30);
                        s += BUS.CaBUS.getHourWork(objSchedules.Day31);
                        itemRow["SumHour"] = (double)s / 60;
                        result.Rows.Add(itemRow);
                        s = 0;
                    }
                }
                i++;
            }

            return result;
        }
        public static bool TaoPhanCa(int UserId, DateTime datebegin, DateTime dateend)
        {
            try
            {
                using (Moved.Moved_DB dc = new Moved_DB(_connString))
                {
                    WIT_Schedule us = new WIT_Schedule();
                    us.UserID = UserId;
                    us.StartTime = datebegin;
                    us.EndTime = dateend;
                    us.Status = 1;
                    us.Day1 = -1;
                    us.Day2 = -1;
                    us.Day3 = -1;
                    us.Day4 = -1;
                    us.Day5 = -1;
                    us.Day6 = -1;
                    us.Day7 = -1;
                    us.Day8 = -1;
                    us.Day9 = -1;
                    us.Day10 = -1;
                    us.Day11 = -1;
                    us.Day12 = -1;
                    us.Day13 = -1;
                    us.Day14 = -1;
                    us.Day15 = -1;
                    us.Day16 = -1;
                    us.Day17 = -1;
                    us.Day18 = -1;
                    us.Day19 = -1;
                    us.Day20 = -1;
                    us.Day21 = -1;
                    us.Day22 = -1;
                    us.Day23 = -1;
                    us.Day24 = -1;
                    us.Day25 = -1;
                    us.Day26 = -1;
                    us.Day27 = -1;
                    us.Day28 = -1;
                    us.Day29 = -1;
                    us.Day30 = -1;
                    us.Day31 = -1;
                    dc.WIT_Schedule.InsertOnSubmit(us);
                    dc.SubmitChanges();
                }
                return true;
            }
            catch
            {
                return false;
            }
        }
        public static DataTable ConvertListToDataTable(List<DTO.UserDetail> userDetails, ref double TotalMealAfter, ref double TotalMealEven, ref double TotalTimeStandar, ref double TotalWorkedTime, ref double TotalLateTime, ref double TotalEarlyTime, ref double TotalEatLateTime)
        {
            List<DTO.UserDetail> localUserDetails = userDetails;
            DataTable result = null;
            double localTotalMealAfter = TotalMealAfter;
            double localTotalMealEven = TotalMealEven;
            double localTotalTimeStandar = TotalTimeStandar;
            double localTotalWorkedTime = TotalWorkedTime;
            double locatTotalLateTime = TotalLateTime;
            double localTotalEarlyTime = TotalEarlyTime;
            double localTotalEatLateTime = TotalEatLateTime;
            if (localUserDetails.Count < 1)
            {
                return result;
            }

            result = new DataTable();

            // Create Columns
            result.Columns.Add("Day");
            result.Columns.Add("Shift");
            result.Columns.Add("TimeComeIn");
            result.Columns.Add("TimeCOutMealAfter");
            result.Columns.Add("TimeCInMealAfter");
            result.Columns.Add("TimeCOutMealEven");
            result.Columns.Add("TimeCInMealEven");
            result.Columns.Add("TimeComeOut");
            result.Columns.Add("TotalTMealAfter");
            result.Columns.Add("TotalTMealEven");
            result.Columns.Add("TotalTGoOut");
            result.Columns.Add("TotalTLatedWork");
            result.Columns.Add("TotalTSoonWork");
            result.Columns.Add("TotalTLatedMeal");
            result.Columns.Add("TotalTStandardWork");
            result.Columns.Add("TotalTimeStandar");
            result.Columns.Add("TotalTNowWork");
            result.Columns.Add("TotalTimeMealAfter");
            result.Columns.Add("TotalTimeMealEven");
            result.Columns.Add("TotalEatLateTime");
            result.Columns.Add("TotalLateTime");
            result.Columns.Add("TotalEarlyTime");
            result.Columns.Add("TotalWorkedTime");

            foreach (var localUserDetail in localUserDetails)
            {
                DTO.UserDetail item = localUserDetail;
                DataRow itemRow = result.NewRow();
                itemRow["Day"] = item.Day;
                itemRow["Shift"] = item.Shift;
                itemRow["TimeComeIn"] = item.ComeInHours;
                itemRow["TimeCOutMealAfter"] = item.ComeOutMealAfternoon;
                itemRow["TimeCInMealAfter"] = item.ComeInMealAfternoon;
                itemRow["TimeCOutMealEven"] = item.ComeOutMealEvening;
                itemRow["TimeCInMealEven"] = item.ComeInMealEvening;
                itemRow["TimeComeOut"] = item.ComeOutHours;
                itemRow["TotalTMealAfter"] = item.TotalMealAfternoon;
                itemRow["TotalTMealEven"] = item.TotalMealEvening;
                itemRow["TotalTGoOut"] = item.TotalGoOut;
                itemRow["TotalTLatedWork"] = item.TotalLatedWorking;
                itemRow["TotalTSoonWork"] = item.TotalSoonGoOut;
                itemRow["TotalTLatedMeal"] = item.LatedMeal;
                itemRow["TotalTStandardWork"] = item.TotalStandardHours;
                itemRow["TotalTNowWork"] = item.TotalWorkedHours;


                result.Rows.Add(itemRow);
            }
            DataRow dt = result.NewRow();
            dt["TotalTimeMealAfter"] = localTotalMealAfter;
            dt["TotalTimeMealEven"] = localTotalMealEven;
            dt["TotalLateTime"] = locatTotalLateTime;
            dt["TotalEarlyTime"] = localTotalEarlyTime;
            dt["TotalWorkedTime"] = localTotalWorkedTime;
            dt["TotalTimeStandar"] = TotalTimeStandar;
            dt["TotalEatLateTime"] = TotalEatLateTime;
            result.Rows.Add(dt);
            return result;
        }

        public static List<UserDetail> GetUserDetailExt(List<Schedule> userSchedules, DateTime time, int departId)
        {
            List<Schedule> localTotalSchedules = userSchedules;
            DateTime localTime = time;
            int localDepartId = departId;

            List<UserDetail> listDetails = new List<UserDetail>();

            for (int i = 1; i < 32; i++)
            {
                UserDetail objItem = new UserDetail();
                objItem.TotalWorkedHours = 0;
                listDetails.Add(objItem);
            }

            using (Moved_DB dc = new Moved_DB(_connString))
            {
                // Get Department's Check Out & Check In
                var checkInOuts = (from cio in dc.AL_CheckInOut
                                   where cio.UserID == localDepartId &&
                                             cio.CheckTime.Month == localTime.Month &&
                                             cio.CheckTime.Year == localTime.Year
                                   select cio
                                        );
                // Get ShiftType
                var shifts = (from s in dc.AL_Shift
                              select s
                                 );
            }

            return listDetails;
        }

        public static List<CheckDetails> GetHistoriesByDepartAndDate(int depart, DateTime time)
        {
            int localDepart = depart;
            DateTime localTime = time;

            List<CheckDetails> listHistories = new List<CheckDetails>();

            using (Moved_DB dc = new Moved_DB(_connString))
            {
                var histories = (from acio in dc.AL_CheckInOut
                                 join aui in dc.AL_UserInfo on acio.UserID equals aui.UserID
                                 where aui.DepartmentID == localDepart &&
                                 acio.CheckTime.Month == localTime.Month &&
                                 acio.CheckTime.Year == localTime.Year
                                 select new CheckDTO
                                     {
                                         CheckInOutID = acio.CheckInOutID,
                                         UserID = acio.UserID,
                                         CheckTime = acio.CheckTime,
                                         ChecType = (int)acio.CheckType,
                                         FullName = aui.FullName
                                     }
                                      );
                var schedules = (from ws in dc.WIT_Schedule
                                 join aui in dc.AL_UserInfo on ws.UserID equals aui.UserID
                                 where aui.DepartmentID == localDepart &&
                                           ws.StartTime.Month == localTime.Month &&
                                           ws.StartTime.Year == localTime.Year
                                 select ws
                                       );

                // Get distinct user from above source
                var users = histories.Select(x => x.UserID).Distinct();

                // Each user in users
                foreach (var user in users)
                {
                    int userID = user;
                    // Create list user detail 
                    GetHistoriesByUser(histories.Where(x => x.UserID == userID).ToList(), ref listHistories, localTime, schedules.Where(x => x.UserID == userID).ToList());
                    // Add list user detail into source
                }

            }

            return listHistories;
        }

        private static string GetShiftCodeByDay(int day, WIT_Schedule schedule)
        {
            int localDay = day;
            WIT_Schedule localSchedule = schedule;
            string result = "";

            switch (localDay)
            {
                case 1:
                    result = GetShiftCodeByID((int)localSchedule.Day1);
                    break;
                case 2:
                    result = GetShiftCodeByID((int)localSchedule.Day2);
                    break;
                case 3:
                    result = GetShiftCodeByID((int)localSchedule.Day3);
                    break;
                case 4:
                    result = GetShiftCodeByID((int)localSchedule.Day4);
                    break;
                case 5:
                    result = GetShiftCodeByID((int)localSchedule.Day5);
                    break;
                case 6:
                    result = GetShiftCodeByID((int)localSchedule.Day6);
                    break;
                case 7:
                    result = GetShiftCodeByID((int)localSchedule.Day7);
                    break;
                case 8:
                    result = GetShiftCodeByID((int)localSchedule.Day8);
                    break;
                case 9:
                    result = GetShiftCodeByID((int)localSchedule.Day9);
                    break;
                case 10:
                    result = GetShiftCodeByID((int)localSchedule.Day10);
                    break;
                case 11:
                    result = GetShiftCodeByID((int)localSchedule.Day11);
                    break;
                case 12:
                    result = GetShiftCodeByID((int)localSchedule.Day12);
                    break;
                case 13:
                    result = GetShiftCodeByID((int)localSchedule.Day13);
                    break;
                case 14:
                    result = GetShiftCodeByID((int)localSchedule.Day14);
                    break;
                case 15:
                    result = GetShiftCodeByID((int)localSchedule.Day15);
                    break;
                case 16:
                    result = GetShiftCodeByID((int)localSchedule.Day16);
                    break;
                case 17:
                    result = GetShiftCodeByID((int)localSchedule.Day17);
                    break;
                case 18:
                    result = GetShiftCodeByID((int)localSchedule.Day18);
                    break;
                case 19:
                    result = GetShiftCodeByID((int)localSchedule.Day19);
                    break;
                case 20:
                    result = GetShiftCodeByID((int)localSchedule.Day20);
                    break;
                case 21:
                    result = GetShiftCodeByID((int)localSchedule.Day21);
                    break;
                case 22:
                    result = GetShiftCodeByID((int)localSchedule.Day22);
                    break;
                case 23:
                    result = GetShiftCodeByID((int)localSchedule.Day23);
                    break;
                case 24:
                    result = GetShiftCodeByID((int)localSchedule.Day24);
                    break;
                case 25:
                    result = GetShiftCodeByID((int)localSchedule.Day25);
                    break;
                case 26:
                    result = GetShiftCodeByID((int)localSchedule.Day26);
                    break;
                case 27:
                    result = GetShiftCodeByID((int)localSchedule.Day27);
                    break;
                case 28:
                    result = GetShiftCodeByID((int)localSchedule.Day28);
                    break;
                case 29:
                    result = GetShiftCodeByID((int)localSchedule.Day29);
                    break;
                case 30:
                    result = GetShiftCodeByID((int)localSchedule.Day30);
                    break;
                case 31:
                    result = GetShiftCodeByID((int)localSchedule.Day31);
                    break;
            }

            return result;
        }

        private static CheckDetails CreateCheckDetailsByUser(List<CheckDTO> checkUser, WIT_Schedule userSchedules)
        {
            CheckDetails localCheckDetails = new CheckDetails();
            List<CheckDTO> localCheckUser = checkUser;
            WIT_Schedule localSchedule = userSchedules;

            if (localCheckUser == null || localCheckUser.Count() < 1 || localSchedule == null)
            {
                return null;
            }

            CheckDTO localFirst = localCheckUser.First();

            localCheckDetails.CheckId = localFirst.CheckInOutID;
            localCheckDetails.UserName = localFirst.FullName;
            localCheckDetails.CheckTime = localFirst.CheckTime.ToShortDateString();
            int totalCheckIn = 0;
            int totalCheckOut = 0;
            int totalCheck = 0;

            foreach (var objItem in localCheckUser)
            {
                if (objItem.ChecType == (int)CheckDTO.CheckTypeE.check_in)
                {
                    totalCheckIn += 1;
                }
                else if (objItem.ChecType == (int)CheckDTO.CheckTypeE.check_out)
                {
                    totalCheckOut += 1;
                }
            }

            string shiftCode = GetShiftCodeByDay(localFirst.CheckTime.Day, localSchedule);

            totalCheck = totalCheckIn + totalCheckOut;

            if (shiftCode.Equals("X") && totalCheck == (int)LichDTO.NumsScheduleE.numsX)
            {
                return null;
            }
            else if (totalCheck == (int)LichDTO.NumsScheduleE.numsO)
            {
                return null;
            }

            localCheckDetails.CheckIntCount = totalCheckIn.ToString();
            localCheckDetails.CheckOutCount = totalCheckOut.ToString();
            localCheckDetails.TotalCount = totalCheck.ToString();
            localCheckDetails.ShiftName = shiftCode;

            return localCheckDetails;
        }

        private static void GetHistoriesByUser(List<CheckDTO> lists, ref List<CheckDetails> listHistories, DateTime time, List<WIT_Schedule> schedules)
        {
            // Get user's histories
            List<CheckDTO> localUserHistories = lists;

            // Get source's histories
            List<CheckDetails> localHistories = listHistories;

            List<WIT_Schedule> localSchedules = schedules;

            // Get local time
            DateTime localTime = time;

            int totalDayInMonth = DateTime.DaysInMonth(localTime.Year, localTime.Month);

            if (localSchedules == null || localSchedules.Count() < 1)
            {
                return;
            }

            WIT_Schedule localSchedule = localSchedules.First();

            for (int i = 1; i < totalDayInMonth + 1; i++)
            {
                CheckDetails tempUser = CreateCheckDetailsByUser(localUserHistories.Where(x => x.CheckTime.Day == i && x.CheckTime.Month == localTime.Month && x.CheckTime.Year == localTime.Year).ToList(), localSchedule);
                if (tempUser != null) { localHistories.Add(tempUser); }
            }

        }

        public static List<Check> GetCheckInOutsByUserAndDate(int userId, DateTime time)
        {
            int localUserId = userId;
            DateTime localTime = time;

            List<Check> listCheckInOuts = new List<Check>();

            using (Moved_DB dc = new Moved_DB(_connString))
            {
                var checkInOuts = (from uio in dc.AL_CheckInOut
                                   where uio.UserID == localUserId &&
                                             uio.CheckTime.Day == localTime.Day &&
                                             uio.CheckTime.Month == localTime.Month &&
                                             uio.CheckTime.Year == localTime.Year
                                   select new
                                   {
                                       mCheckTime = uio.CheckTime,
                                       mCheckType = uio.CheckType
                                   }
                                           );
                foreach (var item in checkInOuts)
                {
                    Check objCheck = new Check();
                    objCheck.CheckTime = item.mCheckTime.TimeOfDay.ToString();
                    objCheck.CheckType = getCheckInOutByID((int)item.mCheckType);
                    listCheckInOuts.Add(objCheck);
                }
            }
            return listCheckInOuts;
        }

        public static string getCheckInOutByID(int checkId)
        {
            string result = "Check In";
            switch (checkId)
            {
                case (int)CheckDTO.CheckTypeE.check_out:
                    result = "Check Out";
                    break;
            }
            return result;
        }

        /*

        public static void GenerateExcelUser(int userId, DateTime time, string templateName, string ouputName, ref double totalMealAfterP, ref double totalMealEvenP, ref double totalStandardP, ref double totalWorkedTimeP, ref double totalLateTimeP, ref double totalEarlyTimeP, ref double totalEatLateTimeP)
        {
            int localUserID = userId;
            string localTemplateName = templateName;
            string localOutputName = ouputName;
            DateTime localTime = time;
            double LocalTotalMealAfter = totalMealAfterP;
            double LocalTotalMealEven = totalMealEvenP;
            double LocalTotalStandard = totalStandardP;
            double LocalTotalWorkedTime = totalWorkedTimeP;
            double LocalTotalLateTime=totalLateTimeP;
            double LocalTotalEarlyTime=totalEarlyTimeP;
            double LocalTotalEatLateTime = totalEatLateTimeP;
            // Make a copy of the template file.
            File.Copy(localTemplateName, localOutputName, true);

            using (Moved dc = new Moved(_connString))            
            // Open the copied template workbook. 
            using (SpreadsheetDocument myWorkbook = SpreadsheetDocument.Open(localOutputName, true))
            {
                // Access the main Workbook part, which contains all references.
                WorkbookPart workbookPart = myWorkbook.WorkbookPart;

                // Get the first worksheet. 
                WorksheetPart worksheetPart = workbookPart.WorksheetParts.ElementAt(1);

                // The SheetData object will contain all the data.
                SheetData sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();

                // Begining Row pointer                       
                int index = 7;

                // Couns number
                int count = 1;

                // Get Employee 's schedule from database                  

                List<UserDetail> empDetail = GetUserDetail(localUserID, localTime, ref LocalTotalMealAfter, ref LocalTotalMealEven, ref LocalTotalStandard, ref  LocalTotalWorkedTime, ref LocalTotalLateTime,ref LocalTotalEarlyTime,ref LocalTotalEatLateTime);

                // For each item in the database, add a Row to SheetData.
                foreach (var item in empDetail)
                {
                    // New Row
                    Row row = new Row();
                    row.RowIndex = (UInt32) index;

                    // Day Cell
                    Cell dayCell = CreateCell(item.Day.ToString(), "A" + index.ToString());
                    // Append Cell to Row
                    row.AppendChild(dayCell);

                    // Shift Cell
                    Cell shiftCell = CreateCell(item.Shift, "B" + index.ToString());
                    // Append Cell to Row

                    row.AppendChild(shiftCell);

                    // ComeIn Cell
                    Cell comeInCell = CreateCell(item.ComeInHours, "C" + index.ToString());
                    // Append Cell to Row
                    row.AppendChild(comeInCell);

                    // Go Out Meal Time Afternoon Cell
                    Cell goOutTimeAfterCell = CreateCell(item.ComeOutMealAfternoon, "D" + index.ToString());
                    // Append Cell to Row
                    row.AppendChild(goOutTimeAfterCell);

                    // Go In Meal Time Afternoon Cell
                    Cell goInTimeAfterCell = CreateCell(item.ComeInMealAfternoon, "E" + index.ToString());
                    // Append Cell to Row
                    row.AppendChild(goInTimeAfterCell);

                    // Go Out Meal Time Even Cell
                    Cell goOutTimeEvenCell = CreateCell(item.ComeOutMealEvening, "F" + index.ToString());
                    // Append Cell to Row
                    row.AppendChild(goOutTimeEvenCell);

                    // Go Out Meal Time Even Cell
                    Cell goInTimeEvenCell = CreateCell(item.ComeInMealEvening, "G" + index.ToString());
                    // Append Cell to Row
                    row.AppendChild(goInTimeEvenCell);

                    // ComeOut Cell
                    Cell comeOutCell = CreateCell(item.ComeOutHours, "H" + index.ToString());
                    // Append Cell to Row
                    row.AppendChild(comeOutCell);

                    // Meal Time Afternoon Cell
                    Cell mealTimeAfterCell = CreateCell(item.TotalMealAfternoon, "I" + index.ToString());
                    // Append Cell to Row
                    row.AppendChild(mealTimeAfterCell);

                    // Meal Time Evening Cell
                    Cell mealTimeEvenCell = CreateCell(item.TotalMealEvening, "J" + index.ToString());
                    // Append Cell to Row
                    row.AppendChild(mealTimeEvenCell);

                    // Go out time Cell
                    Cell goOutTimeCell = CreateCell(item.TotalGoOut.ToString(), "K" + index.ToString());
                    // Append Cell to Row
                    row.AppendChild(goOutTimeCell);

                    // Worked Lated Cell
                    Cell workLatedCell = CreateCell(item.TotalLatedWorking.ToString(), "L" + index.ToString());
                    // Append Cell to Row
                    row.AppendChild(workLatedCell);

                    // Worked Soon Cell
                    Cell workSoonCell = CreateCell(item.TotalSoonGoOut.ToString(), "M" + index.ToString());
                    // Append Cell to Row
                    row.AppendChild(workSoonCell);

                    // Lated Meal Cell
                    Cell latedMealCell = CreateCell(item.LatedMeal.ToString(), "N" + index.ToString());
                    // Append Cell to Row
                    row.AppendChild(latedMealCell);

                    // Standard Time Cell
                    Cell standardTimeCell = CreateCell(item.TotalStandardHours.ToString(), "O" + index.ToString());
                    // Append Cell to Row
                    row.AppendChild(standardTimeCell);

                    // Total work time Time Cell
                    Cell totalWorkedHoursCell = CreateCell(item.TotalWorkedHours.ToString(), "P" + index.ToString());
                    // Append Cell to Row
                    row.AppendChild(totalWorkedHoursCell);

                    // Append Row to SheetData
                    sheetData.AppendChild(row);

                    count++;
                    index++;
                }
                // save
                worksheetPart.Worksheet.Save();
            }
        }
        */
        public static List<UserDetail> GetUserDetail(int userId, DateTime time, ref double totalMealAfterP, ref double totalMealEvenP, ref double totalStandardP, ref double totalWorkedTimeP, ref double totalLateTimeP, ref double totalEarlyTimeP, ref double totalEatLateTimeP)
        {
            int localUserId = userId;
            DateTime localTime = time;

            double totalMealAfter = totalMealAfterP;
            double totalMealEven = totalMealEvenP;
            double totalStandard = totalStandardP;
            double totalWorkedTime = totalWorkedTimeP;
            double totalLateTime = totalLateTimeP;
            double totalEarlyTime = totalEarlyTimeP;
            double totalEatLateTime = totalEatLateTimeP;

            List<UserDetail> listDetail = new List<UserDetail>();

            using (Moved_DB dc = new Moved_DB(_connString))
            {
                // Get CheckIn - CheckOut
                var checkInOuts = (from cio in dc.AL_CheckInOut
                                   where cio.UserID == localUserId &&
                                             cio.CheckTime.Month == time.Month &&
                                             cio.CheckTime.Year == time.Year
                                   select cio
                                          );

                // Get ShiftType
                var shifts = (from s in dc.AL_Shift
                              select s
                                 );

                // Get User Schedule
                var userSchedules = (from ws in dc.WIT_Schedule
                                     where ws.UserID == localUserId &&
                                               ws.StartTime.Month == localTime.Month &&
                                               ws.StartTime.Year == localTime.Year
                                     select ws
                                             );

                foreach (var item in userSchedules)
                {
                    // Get Hours of this day = HDay1 (1. data, 2. null)
                    string hday1 = (item.HDay1 != "") ? item.HDay1 : CaDTO.ShiftE.Default.ToString();
                    int day1 = (Convert.ToString(item.Day1) != "") ? (int)item.Day1 : (int)CaDTO.ShiftE.Default;
                    // More pass HDay1 in CreateUserDetailExt function
                    UserDetail day01 = CreateUserDetailExt(1, day1, hday1, shifts.Where(a => a.ShiftID == day1).ToList(), checkInOuts.Where(a => a.UserID == localUserId && a.CheckTime.Month == localTime.Month && a.CheckTime.Year == localTime.Year && a.CheckTime.Day == 1).ToList(), ref totalMealAfter, ref totalMealEven, ref totalStandard, ref totalWorkedTime, ref totalLateTime, ref totalEarlyTime, ref totalEatLateTime);
                    listDetail.Add(day01);

                    string hday2 = (item.HDay2 != "") ? item.HDay2 : CaDTO.ShiftE.Default.ToString();
                    int day2 = (Convert.ToString(item.Day2) != "") ? (int)item.Day2 : (int)CaDTO.ShiftE.Default;
                    UserDetail day02 = CreateUserDetailExt(2, day2, hday2, shifts.Where(a => a.ShiftID == day2).ToList(), checkInOuts.Where(a => a.UserID == localUserId && a.CheckTime.Month == localTime.Month && a.CheckTime.Year == localTime.Year && a.CheckTime.Day == 2).ToList(), ref totalMealAfter, ref totalMealEven, ref totalStandard, ref totalWorkedTime, ref totalLateTime, ref totalEarlyTime, ref totalEatLateTime);
                    listDetail.Add(day02);

                    string hday3 = (item.HDay3 != "") ? item.HDay3 : CaDTO.ShiftE.Default.ToString();
                    int day3 = (Convert.ToString(item.Day3) != "") ? (int)item.Day3 : (int)CaDTO.ShiftE.Default;
                    UserDetail day03 = CreateUserDetailExt(3, day3, hday3, shifts.Where(a => a.ShiftID == day3).ToList(), checkInOuts.Where(a => a.UserID == localUserId && a.CheckTime.Month == localTime.Month && a.CheckTime.Year == localTime.Year && a.CheckTime.Day == 3).ToList(), ref totalMealAfter, ref totalMealEven, ref totalStandard, ref totalWorkedTime, ref totalLateTime, ref totalEarlyTime, ref totalEatLateTime);
                    listDetail.Add(day03);

                    string hday4 = (item.HDay4 != "") ? item.HDay4 : CaDTO.ShiftE.Default.ToString();
                    int day4 = (Convert.ToString(item.Day4) != "") ? (int)item.Day4 : (int)CaDTO.ShiftE.Default;
                    UserDetail day04 = CreateUserDetailExt(4, day4, hday4, shifts.Where(a => a.ShiftID == day4).ToList(), checkInOuts.Where(a => a.UserID == localUserId && a.CheckTime.Month == localTime.Month && a.CheckTime.Year == localTime.Year && a.CheckTime.Day == 4).ToList(), ref totalMealAfter, ref totalMealEven, ref totalStandard, ref totalWorkedTime, ref totalLateTime, ref totalEarlyTime, ref totalEatLateTime);
                    listDetail.Add(day04);

                    string hday5 = (item.HDay5 != "") ? item.HDay5 : CaDTO.ShiftE.Default.ToString();
                    int day5 = (Convert.ToString(item.Day5) != "") ? (int)item.Day5 : (int)CaDTO.ShiftE.Default;
                    UserDetail day05 = CreateUserDetailExt(5, day5, hday5, shifts.Where(a => a.ShiftID == day5).ToList(), checkInOuts.Where(a => a.UserID == localUserId && a.CheckTime.Month == localTime.Month && a.CheckTime.Year == localTime.Year && a.CheckTime.Day == 5).ToList(), ref totalMealAfter, ref totalMealEven, ref totalStandard, ref totalWorkedTime, ref totalLateTime, ref totalEarlyTime, ref totalEatLateTime);
                    listDetail.Add(day05);

                    string hday6 = (item.HDay6 != "") ? item.HDay6 : CaDTO.ShiftE.Default.ToString();
                    int day6 = (Convert.ToString(item.Day6) != "") ? (int)item.Day6 : (int)CaDTO.ShiftE.Default;
                    UserDetail day06 = CreateUserDetailExt(6, day6, hday6, shifts.Where(a => a.ShiftID == day6).ToList(), checkInOuts.Where(a => a.UserID == localUserId && a.CheckTime.Month == localTime.Month && a.CheckTime.Year == localTime.Year && a.CheckTime.Day == 6).ToList(), ref totalMealAfter, ref totalMealEven, ref totalStandard, ref totalWorkedTime, ref totalLateTime, ref totalEarlyTime, ref totalEatLateTime);
                    listDetail.Add(day06);

                    string hday7 = (item.HDay7 != "") ? item.HDay7 : CaDTO.ShiftE.Default.ToString();
                    int day7 = (Convert.ToString(item.Day7) != "") ? (int)item.Day7 : (int)CaDTO.ShiftE.Default;
                    UserDetail day07 = CreateUserDetailExt(7, day7, hday7, shifts.Where(a => a.ShiftID == day7).ToList(), checkInOuts.Where(a => a.UserID == localUserId && a.CheckTime.Month == localTime.Month && a.CheckTime.Year == localTime.Year && a.CheckTime.Day == 7).ToList(), ref totalMealAfter, ref totalMealEven, ref totalStandard, ref totalWorkedTime, ref totalLateTime, ref totalEarlyTime, ref totalEatLateTime);
                    listDetail.Add(day07);

                    string hday8 = (item.HDay8 != "") ? item.HDay8 : CaDTO.ShiftE.Default.ToString();
                    int day8 = (Convert.ToString(item.Day8) != "") ? (int)item.Day8 : (int)CaDTO.ShiftE.Default;
                    UserDetail day08 = CreateUserDetailExt(8, day8, hday8, shifts.Where(a => a.ShiftID == day8).ToList(), checkInOuts.Where(a => a.UserID == localUserId && a.CheckTime.Month == localTime.Month && a.CheckTime.Year == localTime.Year && a.CheckTime.Day == 8).ToList(), ref totalMealAfter, ref totalMealEven, ref totalStandard, ref totalWorkedTime, ref totalLateTime, ref totalEarlyTime, ref totalEatLateTime);
                    listDetail.Add(day08);

                    string hday9 = (item.HDay9 != "") ? item.HDay9 : CaDTO.ShiftE.Default.ToString();
                    int day9 = (Convert.ToString(item.Day9) != "") ? (int)item.Day9 : (int)CaDTO.ShiftE.Default;
                    UserDetail day09 = CreateUserDetailExt(9, day9, hday9, shifts.Where(a => a.ShiftID == day9).ToList(), checkInOuts.Where(a => a.UserID == localUserId && a.CheckTime.Month == localTime.Month && a.CheckTime.Year == localTime.Year && a.CheckTime.Day == 9).ToList(), ref totalMealAfter, ref totalMealEven, ref totalStandard, ref totalWorkedTime, ref totalLateTime, ref totalEarlyTime, ref totalEatLateTime);
                    listDetail.Add(day09);

                    string hday10 = (item.HDay10 != "") ? item.HDay10 : CaDTO.ShiftE.Default.ToString();
                    int days10 = (Convert.ToString(item.Day10) != "") ? (int)item.Day10 : (int)CaDTO.ShiftE.Default;
                    UserDetail day10 = CreateUserDetailExt(10, days10, hday10, shifts.Where(a => a.ShiftID == days10).ToList(), checkInOuts.Where(a => a.UserID == localUserId && a.CheckTime.Month == localTime.Month && a.CheckTime.Year == localTime.Year && a.CheckTime.Day == 10).ToList(), ref totalMealAfter, ref totalMealEven, ref totalStandard, ref totalWorkedTime, ref totalLateTime, ref totalEarlyTime, ref totalEatLateTime);
                    listDetail.Add(day10);

                    string hday11 = (item.HDay11 != "") ? item.HDay11 : CaDTO.ShiftE.Default.ToString();
                    int days11 = (Convert.ToString(item.Day11) != "") ? (int)item.Day11 : (int)CaDTO.ShiftE.Default;
                    UserDetail day11 = CreateUserDetailExt(11, days11, hday11, shifts.Where(a => a.ShiftID == days11).ToList(), checkInOuts.Where(a => a.UserID == localUserId && a.CheckTime.Month == localTime.Month && a.CheckTime.Year == localTime.Year && a.CheckTime.Day == 11).ToList(), ref totalMealAfter, ref totalMealEven, ref totalStandard, ref totalWorkedTime, ref totalLateTime, ref totalEarlyTime, ref totalEatLateTime);
                    listDetail.Add(day11);

                    string hday12 = (item.HDay12 != "") ? item.HDay12 : CaDTO.ShiftE.Default.ToString();
                    int days12 = (Convert.ToString(item.Day12) != "") ? (int)item.Day12 : (int)CaDTO.ShiftE.Default;
                    UserDetail day12 = CreateUserDetailExt(12, days12, hday12, shifts.Where(a => a.ShiftID == days12).ToList(), checkInOuts.Where(a => a.UserID == localUserId && a.CheckTime.Month == localTime.Month && a.CheckTime.Year == localTime.Year && a.CheckTime.Day == 12).ToList(), ref totalMealAfter, ref totalMealEven, ref totalStandard, ref totalWorkedTime, ref totalLateTime, ref totalEarlyTime, ref totalEatLateTime);
                    listDetail.Add(day12);

                    string hday13 = (item.HDay13 != "") ? item.HDay13 : CaDTO.ShiftE.Default.ToString();
                    int days13 = (Convert.ToString(item.Day13) != "") ? (int)item.Day13 : (int)CaDTO.ShiftE.Default;
                    UserDetail day13 = CreateUserDetailExt(13, days13, hday13, shifts.Where(a => a.ShiftID == days13).ToList(), checkInOuts.Where(a => a.UserID == localUserId && a.CheckTime.Month == localTime.Month && a.CheckTime.Year == localTime.Year && a.CheckTime.Day == 13).ToList(), ref totalMealAfter, ref totalMealEven, ref totalStandard, ref totalWorkedTime, ref totalLateTime, ref totalEarlyTime, ref totalEatLateTime);
                    listDetail.Add(day13);

                    string hday14 = (item.HDay14 != "") ? item.HDay14 : CaDTO.ShiftE.Default.ToString();
                    int days14 = (Convert.ToString(item.Day14) != "") ? (int)item.Day14 : (int)CaDTO.ShiftE.Default;
                    UserDetail day14 = CreateUserDetailExt(14, days14, hday14, shifts.Where(a => a.ShiftID == days14).ToList(), checkInOuts.Where(a => a.UserID == localUserId && a.CheckTime.Month == localTime.Month && a.CheckTime.Year == localTime.Year && a.CheckTime.Day == 14).ToList(), ref totalMealAfter, ref totalMealEven, ref totalStandard, ref totalWorkedTime, ref totalLateTime, ref totalEarlyTime, ref totalEatLateTime);
                    listDetail.Add(day14);

                    string hday15 = (item.HDay15 != "") ? item.HDay15 : CaDTO.ShiftE.Default.ToString();
                    int days15 = (Convert.ToString(item.Day15) != "") ? (int)item.Day15 : (int)CaDTO.ShiftE.Default;
                    UserDetail day15 = CreateUserDetailExt(15, days15, hday15, shifts.Where(a => a.ShiftID == days15).ToList(), checkInOuts.Where(a => a.UserID == localUserId && a.CheckTime.Month == localTime.Month && a.CheckTime.Year == localTime.Year && a.CheckTime.Day == 15).ToList(), ref totalMealAfter, ref totalMealEven, ref totalStandard, ref totalWorkedTime, ref totalLateTime, ref totalEarlyTime, ref totalEatLateTime);
                    listDetail.Add(day15);

                    string hday16 = (item.HDay16 != "") ? item.HDay16 : CaDTO.ShiftE.Default.ToString();
                    int days16 = (Convert.ToString(item.Day16) != "") ? (int)item.Day16 : (int)CaDTO.ShiftE.Default;
                    UserDetail day16 = CreateUserDetailExt(16, days16, hday16, shifts.Where(a => a.ShiftID == days16).ToList(), checkInOuts.Where(a => a.UserID == localUserId && a.CheckTime.Month == localTime.Month && a.CheckTime.Year == localTime.Year && a.CheckTime.Day == 16).ToList(), ref totalMealAfter, ref totalMealEven, ref totalStandard, ref totalWorkedTime, ref totalLateTime, ref totalEarlyTime, ref totalEatLateTime);
                    listDetail.Add(day16);

                    string hday17 = (item.HDay17 != "") ? item.HDay17 : CaDTO.ShiftE.Default.ToString();
                    int days17 = (Convert.ToString(item.Day17) != "") ? (int)item.Day17 : (int)CaDTO.ShiftE.Default;
                    UserDetail day17 = CreateUserDetailExt(17, days17, hday17, shifts.Where(a => a.ShiftID == days17).ToList(), checkInOuts.Where(a => a.UserID == localUserId && a.CheckTime.Month == localTime.Month && a.CheckTime.Year == localTime.Year && a.CheckTime.Day == 17).ToList(), ref totalMealAfter, ref totalMealEven, ref totalStandard, ref totalWorkedTime, ref totalLateTime, ref totalEarlyTime, ref totalEatLateTime);
                    listDetail.Add(day17);

                    string hday18 = (item.HDay18 != "") ? item.HDay18 : CaDTO.ShiftE.Default.ToString();
                    int days18 = (Convert.ToString(item.Day18) != "") ? (int)item.Day18 : (int)CaDTO.ShiftE.Default;
                    UserDetail day18 = CreateUserDetailExt(18, days18, hday18, shifts.Where(a => a.ShiftID == days18).ToList(), checkInOuts.Where(a => a.UserID == localUserId && a.CheckTime.Month == localTime.Month && a.CheckTime.Year == localTime.Year && a.CheckTime.Day == 18).ToList(), ref totalMealAfter, ref totalMealEven, ref totalStandard, ref totalWorkedTime, ref totalLateTime, ref totalEarlyTime, ref totalEatLateTime);
                    listDetail.Add(day18);

                    string hday19 = (item.HDay19 != "") ? item.HDay19 : CaDTO.ShiftE.Default.ToString();
                    int days19 = (Convert.ToString(item.Day19) != "") ? (int)item.Day19 : (int)CaDTO.ShiftE.Default;
                    UserDetail day19 = CreateUserDetailExt(19, days19, hday19, shifts.Where(a => a.ShiftID == days19).ToList(), checkInOuts.Where(a => a.UserID == localUserId && a.CheckTime.Month == localTime.Month && a.CheckTime.Year == localTime.Year && a.CheckTime.Day == 19).ToList(), ref totalMealAfter, ref totalMealEven, ref totalStandard, ref totalWorkedTime, ref totalLateTime, ref totalEarlyTime, ref totalEatLateTime);
                    listDetail.Add(day19);

                    string hday20 = (item.HDay20 != "") ? item.HDay20 : CaDTO.ShiftE.Default.ToString();
                    int days20 = (Convert.ToString(item.Day20) != "") ? (int)item.Day20 : (int)CaDTO.ShiftE.Default;
                    UserDetail day20 = CreateUserDetailExt(20, days20, hday20, shifts.Where(a => a.ShiftID == days20).ToList(), checkInOuts.Where(a => a.UserID == localUserId && a.CheckTime.Month == localTime.Month && a.CheckTime.Year == localTime.Year && a.CheckTime.Day == 20).ToList(), ref totalMealAfter, ref totalMealEven, ref totalStandard, ref totalWorkedTime, ref totalLateTime, ref totalEarlyTime, ref totalEatLateTime);
                    listDetail.Add(day20);

                    string hday21 = (item.HDay21 != "") ? item.HDay21 : CaDTO.ShiftE.Default.ToString();
                    int days21 = (Convert.ToString(item.Day21) != "") ? (int)item.Day21 : (int)CaDTO.ShiftE.Default;
                    UserDetail day21 = CreateUserDetailExt(21, days21, hday21, shifts.Where(a => a.ShiftID == days21).ToList(), checkInOuts.Where(a => a.UserID == localUserId && a.CheckTime.Month == localTime.Month && a.CheckTime.Year == localTime.Year && a.CheckTime.Day == 21).ToList(), ref totalMealAfter, ref totalMealEven, ref totalStandard, ref totalWorkedTime, ref totalLateTime, ref totalEarlyTime, ref totalEatLateTime);
                    listDetail.Add(day21);

                    string hday22 = (item.HDay22 != "") ? item.HDay22 : CaDTO.ShiftE.Default.ToString();
                    int days22 = (Convert.ToString(item.Day22) != "") ? (int)item.Day22 : (int)CaDTO.ShiftE.Default;
                    UserDetail day22 = CreateUserDetailExt(22, days22, hday22, shifts.Where(a => a.ShiftID == days22).ToList(), checkInOuts.Where(a => a.UserID == localUserId && a.CheckTime.Month == localTime.Month && a.CheckTime.Year == localTime.Year && a.CheckTime.Day == 22).ToList(), ref totalMealAfter, ref totalMealEven, ref totalStandard, ref totalWorkedTime, ref totalLateTime, ref totalEarlyTime, ref totalEatLateTime);
                    listDetail.Add(day22);

                    string hday23 = (item.HDay23 != "") ? item.HDay23 : CaDTO.ShiftE.Default.ToString();
                    int days23 = (Convert.ToString(item.Day23) != "") ? (int)item.Day23 : (int)CaDTO.ShiftE.Default;
                    UserDetail day23 = CreateUserDetailExt(23, days23, hday23, shifts.Where(a => a.ShiftID == days23).ToList(), checkInOuts.Where(a => a.UserID == localUserId && a.CheckTime.Month == localTime.Month && a.CheckTime.Year == localTime.Year && a.CheckTime.Day == 23).ToList(), ref totalMealAfter, ref totalMealEven, ref totalStandard, ref totalWorkedTime, ref totalLateTime, ref totalEarlyTime, ref totalEatLateTime);
                    listDetail.Add(day23);

                    string hday24 = (item.HDay24 != "") ? item.HDay24 : CaDTO.ShiftE.Default.ToString();
                    int days24 = (Convert.ToString(item.Day24) != "") ? (int)item.Day24 : (int)CaDTO.ShiftE.Default;
                    UserDetail day24 = CreateUserDetailExt(24, days24, hday24, shifts.Where(a => a.ShiftID == days24).ToList(), checkInOuts.Where(a => a.UserID == localUserId && a.CheckTime.Month == localTime.Month && a.CheckTime.Year == localTime.Year && a.CheckTime.Day == 24).ToList(), ref totalMealAfter, ref totalMealEven, ref totalStandard, ref totalWorkedTime, ref totalLateTime, ref totalEarlyTime, ref totalEatLateTime);
                    listDetail.Add(day24);

                    string hday25 = (item.HDay25 != "") ? item.HDay25 : CaDTO.ShiftE.Default.ToString();
                    int days25 = (Convert.ToString(item.Day25) != "") ? (int)item.Day25 : (int)CaDTO.ShiftE.Default;
                    UserDetail day25 = CreateUserDetailExt(25, days25, hday25, shifts.Where(a => a.ShiftID == days25).ToList(), checkInOuts.Where(a => a.UserID == localUserId && a.CheckTime.Month == localTime.Month && a.CheckTime.Year == localTime.Year && a.CheckTime.Day == 25).ToList(), ref totalMealAfter, ref totalMealEven, ref totalStandard, ref totalWorkedTime, ref totalLateTime, ref totalEarlyTime, ref totalEatLateTime);
                    listDetail.Add(day25);

                    string hday26 = (item.HDay26 != "") ? item.HDay26 : CaDTO.ShiftE.Default.ToString();
                    int days26 = (Convert.ToString(item.Day26) != "") ? (int)item.Day26 : (int)CaDTO.ShiftE.Default;
                    UserDetail day26 = CreateUserDetailExt(26, days26, hday26, shifts.Where(a => a.ShiftID == days26).ToList(), checkInOuts.Where(a => a.UserID == localUserId && a.CheckTime.Month == localTime.Month && a.CheckTime.Year == localTime.Year && a.CheckTime.Day == 26).ToList(), ref totalMealAfter, ref totalMealEven, ref totalStandard, ref totalWorkedTime, ref totalLateTime, ref totalEarlyTime, ref totalEatLateTime);
                    listDetail.Add(day26);

                    string hday27 = (item.HDay27 != "") ? item.HDay27 : CaDTO.ShiftE.Default.ToString();
                    int days27 = (Convert.ToString(item.Day27) != "") ? (int)item.Day27 : (int)CaDTO.ShiftE.Default;
                    UserDetail day27 = CreateUserDetailExt(27, days27, hday27, shifts.Where(a => a.ShiftID == days27).ToList(), checkInOuts.Where(a => a.UserID == localUserId && a.CheckTime.Month == localTime.Month && a.CheckTime.Year == localTime.Year && a.CheckTime.Day == 27).ToList(), ref totalMealAfter, ref totalMealEven, ref totalStandard, ref totalWorkedTime, ref totalLateTime, ref totalEarlyTime, ref totalEatLateTime);
                    listDetail.Add(day27);


                    if (DateTime.DaysInMonth(localTime.Year, localTime.Month) <= 28)
                    {
                        string hday28 = (item.HDay28 != "") ? item.HDay28 : CaDTO.ShiftE.Default.ToString();
                        int days28 = (Convert.ToString(item.Day28) != "") ? (int)item.Day28 : (int)CaDTO.ShiftE.Default;
                        UserDetail day28 = CreateUserDetailExt(28, days28, hday28, shifts.Where(a => a.ShiftID == days28).ToList(), checkInOuts.Where(a => a.UserID == localUserId && a.CheckTime.Month == localTime.Month && a.CheckTime.Year == localTime.Year && a.CheckTime.Day == 28).ToList(), ref totalMealAfter, ref totalMealEven, ref totalStandard, ref totalWorkedTime, ref totalLateTime, ref totalEarlyTime, ref totalEatLateTime);
                        listDetail.Add(day28);
                    }
                    else if (DateTime.DaysInMonth(localTime.Year, localTime.Month) <= 29)
                    {
                        string hday28 = (item.HDay28 != "") ? item.HDay28 : CaDTO.ShiftE.Default.ToString();
                        int days28 = (Convert.ToString(item.Day28) != "") ? (int)item.Day28 : (int)CaDTO.ShiftE.Default;
                        UserDetail day28 = CreateUserDetailExt(28, days28, hday28, shifts.Where(a => a.ShiftID == days28).ToList(), checkInOuts.Where(a => a.UserID == localUserId && a.CheckTime.Month == localTime.Month && a.CheckTime.Year == localTime.Year && a.CheckTime.Day == 28).ToList(), ref totalMealAfter, ref totalMealEven, ref totalStandard, ref totalWorkedTime, ref totalLateTime, ref totalEarlyTime, ref totalEatLateTime);
                        listDetail.Add(day28);

                        string hday29 = (item.HDay29 != "") ? item.HDay29 : CaDTO.ShiftE.Default.ToString();
                        int days29 = (Convert.ToString(item.Day29) != "") ? (int)item.Day29 : (int)CaDTO.ShiftE.Default;
                        UserDetail day29 = CreateUserDetailExt(29, days29, hday29, shifts.Where(a => a.ShiftID == days29).ToList(), checkInOuts.Where(a => a.UserID == localUserId && a.CheckTime.Month == localTime.Month && a.CheckTime.Year == localTime.Year && a.CheckTime.Day == 29).ToList(), ref totalMealAfter, ref totalMealEven, ref totalStandard, ref totalWorkedTime, ref totalLateTime, ref totalEarlyTime, ref totalEatLateTime);
                        listDetail.Add(day29);
                    }
                    else if (DateTime.DaysInMonth(localTime.Year, localTime.Month) <= 30)
                    {
                        string hday28 = (item.HDay28 != "") ? item.HDay28 : CaDTO.ShiftE.Default.ToString();
                        int days28 = (Convert.ToString(item.Day28) != "") ? (int)item.Day28 : (int)CaDTO.ShiftE.Default;
                        UserDetail day28 = CreateUserDetailExt(28, days28, hday28, shifts.Where(a => a.ShiftID == days28).ToList(), checkInOuts.Where(a => a.UserID == localUserId && a.CheckTime.Month == localTime.Month && a.CheckTime.Year == localTime.Year && a.CheckTime.Day == 28).ToList(), ref totalMealAfter, ref totalMealEven, ref totalStandard, ref totalWorkedTime, ref totalLateTime, ref totalEarlyTime, ref totalEatLateTime);
                        listDetail.Add(day28);

                        string hday29 = (item.HDay29 != "") ? item.HDay29 : CaDTO.ShiftE.Default.ToString();
                        int days29 = (Convert.ToString(item.Day29) != "") ? (int)item.Day29 : (int)CaDTO.ShiftE.Default;
                        UserDetail day29 = CreateUserDetailExt(29, days29, hday29, shifts.Where(a => a.ShiftID == days29).ToList(), checkInOuts.Where(a => a.UserID == localUserId && a.CheckTime.Month == localTime.Month && a.CheckTime.Year == localTime.Year && a.CheckTime.Day == 29).ToList(), ref totalMealAfter, ref totalMealEven, ref totalStandard, ref totalWorkedTime, ref totalLateTime, ref totalEarlyTime, ref totalEatLateTime);
                        listDetail.Add(day29);

                        string hday30 = (item.HDay30 != "") ? item.HDay30 : CaDTO.ShiftE.Default.ToString();
                        int days30 = (Convert.ToString(item.Day30) != "") ? (int)item.Day30 : (int)CaDTO.ShiftE.Default;
                        UserDetail day30 = CreateUserDetailExt(30, days30, hday30, shifts.Where(a => a.ShiftID == days30).ToList(), checkInOuts.Where(a => a.UserID == localUserId && a.CheckTime.Month == localTime.Month && a.CheckTime.Year == localTime.Year && a.CheckTime.Day == 30).ToList(), ref totalMealAfter, ref totalMealEven, ref totalStandard, ref totalWorkedTime, ref totalLateTime, ref totalEarlyTime, ref totalEatLateTime);
                        listDetail.Add(day30);
                    }
                    else
                    {
                        string hday28 = (item.HDay28 != "") ? item.HDay28 : CaDTO.ShiftE.Default.ToString();
                        int days28 = (Convert.ToString(item.Day28) != "") ? (int)item.Day28 : (int)CaDTO.ShiftE.Default;
                        UserDetail day28 = CreateUserDetailExt(28, days28, hday28, shifts.Where(a => a.ShiftID == days28).ToList(), checkInOuts.Where(a => a.UserID == localUserId && a.CheckTime.Month == localTime.Month && a.CheckTime.Year == localTime.Year && a.CheckTime.Day == 28).ToList(), ref totalMealAfter, ref totalMealEven, ref totalStandard, ref totalWorkedTime, ref totalLateTime, ref totalEarlyTime, ref totalEatLateTime);
                        listDetail.Add(day28);

                        string hday29 = (item.HDay29 != "") ? item.HDay29 : CaDTO.ShiftE.Default.ToString();
                        int days29 = (Convert.ToString(item.Day29) != "") ? (int)item.Day29 : (int)CaDTO.ShiftE.Default;
                        UserDetail day29 = CreateUserDetailExt(29, days29, hday29, shifts.Where(a => a.ShiftID == days29).ToList(), checkInOuts.Where(a => a.UserID == localUserId && a.CheckTime.Month == localTime.Month && a.CheckTime.Year == localTime.Year && a.CheckTime.Day == 29).ToList(), ref totalMealAfter, ref totalMealEven, ref totalStandard, ref totalWorkedTime, ref totalLateTime, ref totalEarlyTime, ref totalEatLateTime);
                        listDetail.Add(day29);

                        string hday30 = (item.HDay30 != "") ? item.HDay30 : CaDTO.ShiftE.Default.ToString();
                        int days30 = (Convert.ToString(item.Day30) != "") ? (int)item.Day30 : (int)CaDTO.ShiftE.Default;
                        UserDetail day30 = CreateUserDetailExt(30, days30, hday30, shifts.Where(a => a.ShiftID == days30).ToList(), checkInOuts.Where(a => a.UserID == localUserId && a.CheckTime.Month == localTime.Month && a.CheckTime.Year == localTime.Year && a.CheckTime.Day == 30).ToList(), ref totalMealAfter, ref totalMealEven, ref totalStandard, ref totalWorkedTime, ref totalLateTime, ref totalEarlyTime, ref totalEatLateTime);
                        listDetail.Add(day30);

                        string hday31 = (item.HDay31 != "") ? item.HDay31 : CaDTO.ShiftE.Default.ToString();
                        int days31 = (Convert.ToString(item.Day31) != "") ? (int)item.Day31 : (int)CaDTO.ShiftE.Default;
                        UserDetail day31 = CreateUserDetailExt(31, days31, hday31, shifts.Where(a => a.ShiftID == days31).ToList(), checkInOuts.Where(a => a.UserID == localUserId && a.CheckTime.Month == localTime.Month && a.CheckTime.Year == localTime.Year && a.CheckTime.Day == 31).ToList(), ref totalMealAfter, ref totalMealEven, ref totalStandard, ref totalWorkedTime, ref totalLateTime, ref totalEarlyTime, ref totalEatLateTime);
                        listDetail.Add(day31);
                    }

                }

            }
            totalMealAfterP = totalMealAfter;
            totalMealEvenP = totalMealEven;
            totalStandardP = totalStandard;
            totalWorkedTimeP = totalWorkedTime;
            totalLateTimeP = totalLateTime;
            totalEarlyTimeP = totalEarlyTime;
            totalEatLateTimeP = totalEatLateTime;
            return listDetail;
        }

        public static int GetNumsCheckInOutByShift(int shift)
        {
            int result = 4;
            int localShift = shift;

            switch (localShift)
            {
                case (int)CaDTO.ShiftE.X:
                    result = (int)LichDTO.LichNumsE.numsX;
                    break;
            }

            return result;
        }

        public static string CheckDayBetweenDays(DateTime checkDay, DateTime startDay, DateTime endDay, DateTime balanceDay, ref double minus, int mod)
        {
            string result = "";

            DateTime localCheckDay = checkDay;
            DateTime localStartDay = startDay;
            DateTime localEndDay = endDay;
            DateTime localbalanceDay = balanceDay;
            double localMinus = minus;
            int localmod = mod;

            double timeCheckDay = localCheckDay.TimeOfDay.TotalMinutes;
            double timeStartDay = localStartDay.TimeOfDay.TotalMinutes;
            double timeEndDay = localEndDay.TimeOfDay.TotalMinutes;
            double timeBalanceDay = localbalanceDay.TimeOfDay.TotalMinutes;

            // Check localCheckDay is between localStartDay and localEndDay
            if (timeCheckDay >= timeStartDay && timeCheckDay <= timeEndDay)
            {
                switch (localmod)
                {
                    case 1: localMinus = (timeCheckDay > timeBalanceDay) ? timeCheckDay - timeBalanceDay : localMinus;
                        result = localCheckDay.TimeOfDay.ToString(); break;
                    case 2: localMinus = (timeCheckDay < timeBalanceDay) ? timeBalanceDay - timeCheckDay : localMinus;
                        result = localCheckDay.TimeOfDay.ToString(); break;
                }

            }

            localMinus = Math.Round(localMinus);

            minus = localMinus;

            return result;
        }

        public static UserDetail CreateUserDetailExt(int day, int shiftType, string hday, List<AL_Shift> shitf, List<AL_CheckInOut> listCheckInOut, ref double TotalMealAfter, ref double TotalMealEven, ref double TotalStandard, ref double TotalWorkedTime, ref double TotalLateTime, ref double TotalEarlyTime, ref double TotalEatLateTime)
        {
            int localDay = day;
            int localShiftType = shiftType;            
            // Copy HDay parameter
            // String localHDay = HDay;

            // If localHDay = null
            // Is True
            string localhday = (hday != null) ? hday : null;
            string[] lchd = new string[20];
            if (localhday != null)
            {
                lchd = localhday.Split(' ');
            }
            double localTotalMealAfter = TotalMealAfter;
            double localTotalMealEven = TotalMealEven;
            double localTotalStandard = TotalStandard;
            double localTotalWorkedTime = TotalWorkedTime;
            double localTotalLateTime = TotalLateTime;
            double localTotalEarlyTime = TotalEarlyTime;
            double localTotalEatLateTime = TotalEatLateTime;
            List<AL_Shift> shifts = shitf;
            var localCheckInOuts = listCheckInOut;


            UserDetail result = new UserDetail();
            // Is lich chua xep hay danh sach checkout rong thi return
            if (shifts == null || localCheckInOuts == null || localCheckInOuts.Count < 1 || shifts.Count() < 1)
            {
                result.Day = day;
                result.Shift = "N";
                result.Hday = hday;
                result.ComeInHours = "";
                result.ComeOutMealAfternoon = "";
                result.ComeInMealAfternoon = "";
                result.ComeOutMealEvening = "";
                result.ComeInMealEvening = "";
                result.ComeOutHours = "";
                result.TotalMealAfternoon = "";
                result.TotalMealEvening = "";
                result.TotalGoOut = 0;
                result.TotalLatedWorking = 0;
                result.TotalSoonGoOut = 0;
                result.LatedMeal = 0;
                result.TotalStandardHours = 0;
                result.TotalWorkedHours = 0;

                return result;
            }


            // Is shift null
            AL_Shift localShift = (Convert.ToString(shifts) == "") ? null : shifts.First();

            // Get Day Data
            int itemDay = localDay;
            string itemHday = localhday;

            // Get Shift Data
            string itemShift = (localShift == null) ? ((int)LichDTO.LichTypeE.emptys).ToString() : localShift.ShiftCode;

            var comeInCheck = localCheckInOuts.First();
            double itemTotalLatedWorking = 0;

            // Get ComeInHours Data
            //if hday == null, use the old way
            //else parse hday to time to calculate working time
            string itemComeInHours = "";
            if (hday == null)
            {
                itemComeInHours = CheckDayBetweenDays(comeInCheck.CheckTime, (DateTime)localShift.CheckInTimeFrom, (DateTime)localShift.CheckInTimeTo, (DateTime)localShift.StartTime, ref itemTotalLatedWorking, 1);
                comeInCheck = (itemComeInHours == "") ? new AL_CheckInOut { CheckInOutID = -1 } : comeInCheck;
            }
            else
            {
                itemComeInHours = CheckDayBetweenDays(DateTime.ParseExact(lchd[0],"HH:mm", null), (DateTime)localShift.CheckInTimeFrom, (DateTime)localShift.CheckInTimeTo, (DateTime)localShift.StartTime, ref itemTotalLatedWorking, 1);
                comeInCheck = (itemComeInHours == "") ? new AL_CheckInOut { CheckInOutID = -1 } : comeInCheck;
            }

            var comeOutCheck = localCheckInOuts.Last();
            double itemTotalSoonGoOut = 0;

            // Get ComeOutHours Data
            //same as come-in hour
            string itemComeOutHours;
            if (hday == null)
            {
                itemComeOutHours=CheckDayBetweenDays(comeOutCheck.CheckTime, (DateTime)localShift.CheckOutTimeFrom, (DateTime)localShift.CheckOutTimeTo, (DateTime)localShift.EndTime, ref itemTotalSoonGoOut, 2);
                comeOutCheck = (itemComeOutHours == "") ? new AL_CheckInOut { CheckInOutID = -1 } : comeOutCheck;
            }
            else
            {
                itemComeOutHours=CheckDayBetweenDays(DateTime.ParseExact(lchd[1], "HH:mm", null), (DateTime)localShift.CheckOutTimeFrom, (DateTime)localShift.CheckOutTimeTo, (DateTime)localShift.EndTime, ref itemTotalSoonGoOut, 2);
                comeOutCheck = (itemComeOutHours == "") ? new AL_CheckInOut { CheckInOutID = -1 } : comeOutCheck;
            }
            // 

            var checksNonComeInComeOut = localCheckInOuts.Where(i => i.CheckInOutID != comeInCheck.CheckInOutID && i.CheckInOutID != comeOutCheck.CheckInOutID);

            // Get ComeOutMeal Afternoon Data
            string itemComeOutMealAfternoon = "";
            string itemComeInMealAffternoon = "";
            string itemComeOutMealEvening = "";
            string itemComeInMealEvening = "";
            string itemTotalMealAfternoon = "";
            string itemTotalMealEvening = "";
            double itemTotalGoOut = 0;
            double itemLatedMeal = 0;

            if (checksNonComeInComeOut.Count() > 0)
            {
                double totalTimeMealAfter = 0;
                DateTime beginMealAfternoon = new DateTime(2000, 1, 1, 11, 0, 0);
                DateTime endMealAfternoon = new DateTime(2000, 1, 1, 14, 0, 0);
                double timeBeginMealAfternoon = beginMealAfternoon.TimeOfDay.TotalMinutes;
                double endEndMealAfternoon = endMealAfternoon.TimeOfDay.TotalMinutes;

                var mealAfterChecks = checksNonComeInComeOut.Where(i => (i.CheckTime.TimeOfDay.TotalMinutes >= timeBeginMealAfternoon && i.CheckTime.TimeOfDay.TotalMinutes <= endEndMealAfternoon));

                if (mealAfterChecks.Count() > 0)
                {
                    var beginMealAfterObj = mealAfterChecks.First();

                    if (beginMealAfterObj != null)
                    {
                        itemComeOutMealAfternoon = beginMealAfterObj.CheckTime.TimeOfDay.ToString();
                    }

                    var endMealAfterObj = mealAfterChecks.Last();

                    if (endMealAfterObj != null && beginMealAfterObj != null && endMealAfterObj != beginMealAfterObj)
                    {
                        double endTimeMealAfter = endMealAfterObj.CheckTime.TimeOfDay.TotalMinutes;
                        double startTimeMealAfter = beginMealAfterObj.CheckTime.TimeOfDay.TotalMinutes;

                        // Tranh 2 lan check lien tiep nhau
                        if (endTimeMealAfter - startTimeMealAfter > (double)LichDTO.LichTypeE.minGoOut)
                        {
                            itemComeInMealAffternoon = endMealAfterObj.CheckTime.TimeOfDay.ToString();
                            totalTimeMealAfter = Math.Round(endTimeMealAfter - startTimeMealAfter);
                            itemTotalMealAfternoon = totalTimeMealAfter.ToString();
                        }
                    }
                }

                double totalTimeMealEven = 0;
                DateTime beginMealEven = new DateTime(2000, 1, 1, 17, 0, 0);
                DateTime endMealEven = new DateTime(2000, 1, 1, 20, 0, 0);
                double timeBeginMealEven = beginMealEven.TimeOfDay.TotalMinutes;
                double endEndMealEven = endMealEven.TimeOfDay.TotalMinutes;

                var mealEvenChecks = checksNonComeInComeOut.Where(i => (i.CheckTime.TimeOfDay.TotalMinutes >= timeBeginMealEven && i.CheckTime.TimeOfDay.TotalMinutes <= endEndMealEven));

                if (mealEvenChecks.Count() > 0)
                {
                    var beginMealEvenObj = mealEvenChecks.First();

                    if (beginMealEvenObj != null)
                    {
                        itemComeOutMealEvening = beginMealEvenObj.CheckTime.TimeOfDay.ToString();
                    }

                    var endMealEvenObj = mealEvenChecks.Last();

                    if (endMealEvenObj != null && beginMealEvenObj != null && endMealEvenObj != beginMealEvenObj)
                    {
                        double endTimeMealEven = endMealEvenObj.CheckTime.TimeOfDay.TotalMinutes;
                        double startTimeMealEven = beginMealEvenObj.CheckTime.TimeOfDay.TotalMinutes;

                        // Tranh 2 lan check lien tiep nhau
                        if (endTimeMealEven - startTimeMealEven > (double)LichDTO.LichTypeE.minGoOut)
                        {
                            itemComeInMealEvening = endMealEvenObj.CheckTime.TimeOfDay.ToString();
                            totalTimeMealEven = Math.Round(endTimeMealEven - startTimeMealEven);
                            itemTotalMealEvening = totalTimeMealEven.ToString();
                        }
                    }
                }

                double standardTime = ((DateTime)localShift.MealTimeTo).TimeOfDay.TotalMinutes -
                                      ((DateTime)localShift.MealTimeFrom).TimeOfDay.TotalMinutes;

                double totalMealTime = totalTimeMealAfter + totalTimeMealEven;

                localTotalMealAfter += totalTimeMealAfter;

                localTotalMealEven += totalTimeMealEven;

                itemLatedMeal = (totalMealTime > standardTime) ? Math.Round(totalMealTime - standardTime) : 0;
                localTotalEatLateTime += itemLatedMeal;
                if (checksNonComeInComeOut.Count() > 1)
                {
                    int localIndex = 1;
                    int totalIndex = checksNonComeInComeOut.Count();

                    for (int i = localIndex; i < totalIndex; i += 2)
                    {
                        AL_CheckInOut item = checksNonComeInComeOut.ElementAt(i);
                        if (item != null)
                        {
                            AL_CheckInOut preItem = checksNonComeInComeOut.ElementAt(i - 1);
                            double timeItem = item.CheckTime.TimeOfDay.TotalMinutes;
                            double timePreItem = preItem.CheckTime.TimeOfDay.TotalMinutes;

                            itemTotalGoOut += (timeItem - timePreItem);
                        }
                    }
                }
            }

            Double itemStandardHours = (Convert.ToString(localShift.ToMinutes) != "") ? (Double)localShift.ToMinutes : 0;
            itemStandardHours = Math.Round(itemStandardHours / 60, 2);

            localTotalStandard += itemStandardHours;

            double itemWorkedHours = 0;

            if (!itemComeOutHours.Equals("") && !itemComeInHours.Equals(""))
            {
                itemWorkedHours = (comeOutCheck.CheckTime.TimeOfDay.TotalMinutes - comeInCheck.CheckTime.TimeOfDay.TotalMinutes);

                if (itemWorkedHours > (int)localShift.WorkMinutes)
                {
                    itemWorkedHours = (int)localShift.WorkMinutes - (itemLatedMeal + itemTotalSoonGoOut + itemTotalLatedWorking);
                }
                else
                {
                    itemWorkedHours = itemWorkedHours - (itemLatedMeal + itemTotalSoonGoOut + itemTotalLatedWorking);
                }
                itemWorkedHours = Math.Round(itemWorkedHours / 60, 2);
            }
            else
            {
                itemWorkedHours = (int)localShift.WorkMinutes - (itemLatedMeal + itemTotalSoonGoOut + itemTotalLatedWorking);
                itemWorkedHours = Math.Round(itemWorkedHours / 60, 2);
            }

            localTotalWorkedTime += itemWorkedHours;
            localTotalLateTime += itemTotalLatedWorking;
            localTotalEarlyTime += itemTotalSoonGoOut;
            itemTotalGoOut = Math.Round(itemTotalGoOut);

            result.Day = itemDay;
            result.Shift = itemShift;
            result.Hday = itemHday;
            result.ComeInHours = itemComeInHours;
            result.ComeOutMealAfternoon = itemComeOutMealAfternoon;
            result.ComeInMealAfternoon = itemComeInMealAffternoon;
            result.ComeOutMealEvening = itemComeOutMealEvening;
            result.ComeInMealEvening = itemComeInMealEvening;
            result.ComeOutHours = itemComeOutHours;
            result.TotalMealAfternoon = itemTotalMealAfternoon;
            result.TotalMealEvening = itemTotalMealEvening;
            result.TotalGoOut = itemTotalGoOut;
            result.TotalLatedWorking = itemTotalLatedWorking;
            result.TotalSoonGoOut = itemTotalSoonGoOut;
            result.LatedMeal = itemLatedMeal;
            result.TotalStandardHours = itemStandardHours;
            result.TotalWorkedHours = itemWorkedHours;

            TotalMealAfter = localTotalMealAfter;
            TotalMealEven = localTotalMealEven;
            TotalStandard = localTotalStandard;
            TotalWorkedTime = localTotalWorkedTime;
            TotalLateTime = localTotalLateTime;
            TotalEarlyTime = localTotalEarlyTime;
            TotalEatLateTime = localTotalEatLateTime;

            return result;
        }

        public static UserDetail CreateUserDetail(int day, int shiftType, List<AL_Shift> shitf, List<AL_CheckInOut> listCheckInOut)
        {
            //int localDay = day;
            int localShiftType = shiftType;
            AL_Shift localShift = shitf.First();
            double totalTimeMealAfter = 0;
            double totalTimeMealEven = 0;
            UserDetail localDetail = new UserDetail();

            var localCheckInOuts = listCheckInOut;

            var listCheckInDays = localCheckInOuts.Where(i => i.CheckType == (int)CheckDTO.CheckTypeE.check_in);
            var listCheckOutDays = localCheckInOuts.Where(j => j.CheckType == (int)CheckDTO.CheckTypeE.check_out);

            AL_CheckInOut comeIn = (listCheckInDays.Count() < 1) ? new AL_CheckInOut { CheckTime = new DateTime(2000, 1, 1, 0, 0, 0) } : listCheckInDays.First();
            AL_CheckInOut comeOut = (listCheckInDays.Count() < 1) ? new AL_CheckInOut { CheckTime = new DateTime(2000, 1, 1, 0, 0, 0) } : listCheckOutDays.Last();

            int count = listCheckInDays.Count() + listCheckOutDays.Count();

            if (count % 2 != 0 || count != GetNumsCheckInOutByShift(localShift.ShiftID) || localShiftType == (int)CaDTO.ShiftE.Default)
            {
                localDetail.Day = day;
                localDetail.Shift = (localShiftType != (int)CaDTO.ShiftE.Default) ? localShift.ShiftCode : "";
                localDetail.ComeInHours = (Convert.ToString(comeIn.CheckTime) != "") ? comeIn.CheckTime.TimeOfDay.ToString() : "";
                localDetail.ComeOutHours = (Convert.ToString(comeOut.CheckTime) != "") ? comeOut.CheckTime.TimeOfDay.ToString() : "";
                localDetail.TotalMealAfternoon = "0";
                localDetail.TotalMealEvening = "0";
                localDetail.TotalGoOut = 0;
                localDetail.TotalLatedWorking = 0;
                localDetail.TotalSoonGoOut = 0;
                localDetail.LatedMeal = 0;
                localDetail.TotalStandardHours = 0;
                localDetail.TotalWorkedHours = 0;

                return localDetail;
            }



            // Get begin time afternoon meal at 11:30 AM
            double beginAfterMeal = (new DateTime(2000, 1, 1, 11, 30, 0)).TimeOfDay.TotalMinutes;
            // Get end time afternoon meal at 2:30 PM
            double endAfterMeal = (new DateTime(2000, 1, 1, 14, 30, 0)).TimeOfDay.TotalMinutes;

            // Get check in time in afternoon meal time ranges
            var checkInMealAfternoons = listCheckInDays.Where(i => (i.CheckTime.TimeOfDay.TotalMinutes >= beginAfterMeal && i.CheckTime.TimeOfDay.TotalMinutes <= endAfterMeal));
            // Get check out time in afternoon meal time ranges
            var checkOutMealAfternoons = listCheckOutDays.Where(i => (i.CheckTime.TimeOfDay.TotalMinutes >= beginAfterMeal && i.CheckTime.TimeOfDay.TotalMinutes <= endAfterMeal));

            if (checkInMealAfternoons.Count() > 0 && checkOutMealAfternoons.Count() > 0)
            {
                var checkInMealAfternoon = checkInMealAfternoons.First();
                var checkOutMealAfternoon = checkOutMealAfternoons.First();

                // Total meal afternoon 
                totalTimeMealAfter = checkInMealAfternoon.CheckTime.TimeOfDay.TotalMinutes - checkOutMealAfternoon.CheckTime.TimeOfDay.TotalMinutes;
                totalTimeMealAfter = Math.Round(totalTimeMealAfter);
            }



            // Get begin time evening meal at 5:30 AM
            double beginEvenMeal = (new DateTime(2000, 1, 1, 17, 30, 0)).TimeOfDay.TotalMinutes;
            // Get end time evening meal 7:30 PM
            double endEvenMeal = (new DateTime(2000, 1, 1, 19, 30, 0)).TimeOfDay.TotalMinutes;

            // Get check in time in evening meal time ranges
            var checkInMealEvens = listCheckInDays.Where(t => (t.CheckTime.TimeOfDay.TotalMinutes >= beginEvenMeal && t.CheckTime.TimeOfDay.TotalMinutes <= endEvenMeal));
            // Get check out time in evening meal time ranges
            var checkOutMealEvens = listCheckOutDays.Where(t => (t.CheckTime.TimeOfDay.TotalMinutes >= beginEvenMeal && t.CheckTime.TimeOfDay.TotalMinutes <= endEvenMeal));

            if (checkInMealEvens.Count() > 0 && checkOutMealEvens.Count() > 0)
            {
                var checkInMealEven = checkInMealEvens.First();
                var checkOutMealEven = checkOutMealEvens.First();
                // Total meal even
                totalTimeMealEven = checkInMealEven.CheckTime.TimeOfDay.TotalMinutes - checkOutMealEven.CheckTime.TimeOfDay.TotalMinutes;
                totalTimeMealEven = Math.Round(totalTimeMealEven);
            }

            // Calc total latedTime
            double latedTimeComeIn = (comeIn.CheckTime.TimeOfDay.TotalMinutes > localShift.StartTime.TimeOfDay.TotalMinutes)
                                   ? localShift.StartTime.TimeOfDay.TotalMinutes -
                                     comeIn.CheckTime.TimeOfDay.TotalMinutes
                                   : 0;
            latedTimeComeIn = Math.Round(latedTimeComeIn);
            // Calc total soonTime
            double soonTimeGoOut = (comeOut.CheckTime.TimeOfDay.TotalMinutes < localShift.EndTime.TimeOfDay.TotalMinutes)
                                       ? localShift.EndTime.TimeOfDay.TotalMinutes -
                                         comeOut.CheckTime.TimeOfDay.TotalMinutes
                                       : 0;
            soonTimeGoOut = Math.Round(soonTimeGoOut);

            // Get standard time
            int standardTime = (int)Math.Round((double)localShift.ToMinutes / 60);

            // Get total worked hours
            double workedHours = comeOut.CheckTime.TimeOfDay.TotalMinutes - comeIn.CheckTime.TimeOfDay.TotalMinutes;
            workedHours = Math.Round(workedHours / 60);

            localDetail.Day = day;
            localDetail.Shift = localShift.ShiftCode;
            localDetail.ComeInHours = comeIn.CheckTime.TimeOfDay.ToString();
            localDetail.ComeOutHours = comeOut.CheckTime.TimeOfDay.ToString();
            localDetail.TotalMealAfternoon = totalTimeMealAfter.ToString();
            localDetail.TotalMealEvening = totalTimeMealEven.ToString();
            localDetail.TotalGoOut = 0;
            localDetail.TotalLatedWorking = latedTimeComeIn;
            localDetail.TotalSoonGoOut = soonTimeGoOut;
            localDetail.LatedMeal = 0;
            localDetail.TotalStandardHours = standardTime;
            localDetail.TotalWorkedHours = workedHours;

            return localDetail;
        }

        /*
        public static Cell CreateCell(string value, string indexCell)
        {
            string localValue = value;
            string localIndexCell = indexCell;

            // FullName Cell
            Cell localCell = new Cell();
            localCell.DataType = CellValues.InlineString;            
            // Column A1, 2, 3 ... and so on
            localCell.CellReference = localIndexCell;
            localCell.CellValue = new CellValue(localValue);
            localCell.DataType = new EnumValue<CellValues>(CellValues.String);
            
            // Create Text object
            //Text localCellText = new Text();
            //localCellText.Text = localValue;
            
            // Append Text to InlineString object
            //InlineString inlineString = new InlineString();
            //inlineString.AppendChild(localCellText);
            
            // Append InlineString to Cell
            //localCell.AppendChild(inlineString);

            return localCell;
        }
        
        public static void GenerateExcelByDepartmentAndTime(string outputName, string templateName, int departID, DateTime time)
        {
            string localOutputName = outputName;
            string localTemplateName = templateName;
            int localDepartID = departID;
            DateTime localTime = time;

            // Make a copy of the template file.
            File.Copy(localTemplateName, localOutputName, true);


            using (Moved dc = new Moved_DB(_connString))            
            // Open the copied template workbook. 
            using (SpreadsheetDocument myWorkbook = SpreadsheetDocument.Open(localOutputName, true))
            {
                // Access the main Workbook part, which contains all references.
                WorkbookPart workbookPart = myWorkbook.WorkbookPart;

                // Get the first worksheet. 
                WorksheetPart worksheetPart = workbookPart.WorksheetParts.ElementAt(1);

                // The SheetData object will contain all the data.
                SheetData sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();

                // Begining Row pointer                       
                int index = 6;

                // Couns number
                int count = 1;

                // Get Employee 's schedule from database
                var empSchedules = (from ws in dc.WIT_Schedule
                                    join ue in dc.AL_UserInfo on ws.UserID equals ue.UserID
                                    where ue.DepartmentID == localDepartID &&
                                          ws.StartTime.Month == localTime.Month &&
                                          ws.StartTime.Year == localTime.Year
                                    select new DTO.Schedule
                                               {
                                                   UserId = ue.UserID,
                                                   FullName = ue.FullName,
                                                   Day1 = (int) ws.Day1,
                                                   Day2 = (int)ws.Day2,
                                                   Day3 = (int)ws.Day3,
                                                   Day4 = (int)ws.Day4,
                                                   Day5 = (int)ws.Day5,
                                                   Day6 = (int)ws.Day6,
                                                   Day7 = (int)ws.Day7,
                                                   Day8 = (int)ws.Day8,
                                                   Day9 = (int)ws.Day9,
                                                   Day10 = (int)ws.Day10,
                                                   Day11 = (int)ws.Day11,
                                                   Day12 = (int)ws.Day12,
                                                   Day13 = (int)ws.Day13,
                                                   Day14 = (int)ws.Day14,
                                                   Day15 = (int)ws.Day15,
                                                   Day16 = (int)ws.Day16,
                                                   Day17 = (int)ws.Day17,
                                                   Day18 = (int)ws.Day18,
                                                   Day19 = (int)ws.Day19,
                                                   Day20 = (int)ws.Day20,
                                                   Day21 = (int)ws.Day21,
                                                   Day22 = (int)ws.Day22,
                                                   Day23 = (int)ws.Day23,
                                                   Day24 = (int)ws.Day24,
                                                   Day25 = (int)ws.Day25,
                                                   Day26 = (int)ws.Day26,
                                                   Day27 = (int)ws.Day27,
                                                   Day28 = (int)ws.Day28,
                                                   Day29 = (int)ws.Day29,
                                                   Day30 = (int)ws.Day30,
                                                   Day31 = (int)ws.Day31
                                                }
                                              );                

                // For each item in the database, add a Row to SheetData.
                foreach (var item in empSchedules)
                {
                    // New Row
                    Row row = new Row();
                    row.RowIndex = (UInt32)index;

                    //Row rowHours = new Row();
                    //rowHours.RowIndex = (UInt32)index++;

                    // Nums Cell
                    Cell numsCell = CreateCell(count.ToString(), "A" + index.ToString());
                    // Append Cell to Row
                    row.AppendChild(numsCell);

                    // FullName Cell
                    Cell fullNameCell = CreateCell(item.FullName, "B" + index.ToString());
                    row.AppendChild(fullNameCell);
                    
                    // Day 1 Cell
                    Cell day1Cell = CreateCell(GetShiftCodeByID((int)item.Day1), "D" + index.ToString());
                    row.AppendChild(day1Cell);

                    // Hour 1 Cell
                    //Cell hours1Cell = CreateCell(GetShiftCodeByID((int)item.Day1), "D" + index.ToString());
                    //rowHours.AppendChild(hours1Cell);

                    // Day 2 Cell
                    Cell day2Cell = CreateCell(GetShiftCodeByID((int)item.Day2), "E" + index.ToString());
                    row.AppendChild(day2Cell);

                    // Day 3 Cell
                    Cell day3Cell = CreateCell(GetShiftCodeByID((int)item.Day3), "F" + index.ToString());
                    row.AppendChild(day3Cell);

                    // Day 4 Cell
                    Cell day4Cell = CreateCell(GetShiftCodeByID((int)item.Day4), "G" + index.ToString());
                    row.AppendChild(day4Cell);

                    // Day 5 Cell
                    Cell day5Cell = CreateCell(GetShiftCodeByID((int)item.Day5), "H" + index.ToString());
                    row.AppendChild(day5Cell);

                    // Day 6 Cell
                    Cell day6Cell = CreateCell(GetShiftCodeByID((int)item.Day6), "I" + index.ToString());
                    row.AppendChild(day6Cell);

                    // Day 7 Cell
                    Cell day7Cell = CreateCell(GetShiftCodeByID((int)item.Day7), "J" + index.ToString());
                    row.AppendChild(day7Cell);

                    // Day 8 Cell
                    Cell day8Cell = CreateCell(GetShiftCodeByID((int)item.Day8), "K" + index.ToString());
                    row.AppendChild(day8Cell);

                    // Day 9 Cell
                    Cell day9Cell = CreateCell(GetShiftCodeByID((int)item.Day9), "L" + index.ToString());
                    row.AppendChild(day9Cell);

                    // Day 10 Cell
                    Cell day10Cell = CreateCell(GetShiftCodeByID((int)item.Day10), "M" + index.ToString());
                    row.AppendChild(day10Cell);

                    // Day 11 Cell
                    Cell day11Cell = CreateCell(GetShiftCodeByID((int)item.Day11), "N" + index.ToString());
                    row.AppendChild(day11Cell);

                    // Day 12 Cell
                    Cell day12Cell = CreateCell(GetShiftCodeByID((int)item.Day12), "O" + index.ToString());
                    row.AppendChild(day12Cell);

                    // Day 13 Cell
                    Cell day13Cell = CreateCell(GetShiftCodeByID((int)item.Day13), "P" + index.ToString());
                    row.AppendChild(day13Cell);

                    // Day 14 Cell
                    Cell day14Cell = CreateCell(GetShiftCodeByID((int)item.Day14), "Q" + index.ToString());
                    row.AppendChild(day14Cell);

                    // Day 15 Cell
                    Cell day15Cell = CreateCell(GetShiftCodeByID((int)item.Day15), "R" + index.ToString());
                    row.AppendChild(day15Cell);

                    // Day 16 Cell
                    Cell day16Cell = CreateCell(GetShiftCodeByID((int)item.Day16), "S" + index.ToString());
                    row.AppendChild(day16Cell);

                    // Day 17 Cell
                    Cell day17Cell = CreateCell(GetShiftCodeByID((int)item.Day17), "T" + index.ToString());
                    row.AppendChild(day17Cell);

                    // Day 18 Cell
                    Cell day18Cell = CreateCell(GetShiftCodeByID((int)item.Day18), "U" + index.ToString());
                    row.AppendChild(day18Cell);

                    // Day 19 Cell
                    Cell day19Cell = CreateCell(GetShiftCodeByID((int)item.Day19), "V" + index.ToString());
                    row.AppendChild(day19Cell);

                    // Day 20 Cell
                    Cell day20Cell = CreateCell(GetShiftCodeByID((int)item.Day20), "W" + index.ToString());
                    row.AppendChild(day20Cell);

                    // Day 21 Cell
                    Cell day21Cell = CreateCell(GetShiftCodeByID((int)item.Day21), "X" + index.ToString());
                    row.AppendChild(day21Cell);

                    // Day 22 Cell
                    Cell day22Cell = CreateCell(GetShiftCodeByID((int)item.Day22), "Y" + index.ToString());
                    row.AppendChild(day22Cell);

                    // Day 23 Cell
                    Cell day23Cell = CreateCell(GetShiftCodeByID((int)item.Day23), "Z" + index.ToString());
                    row.AppendChild(day23Cell);

                    // Day 24 Cell
                    Cell day24Cell = CreateCell(GetShiftCodeByID((int)item.Day24), "AA" + index.ToString());
                    row.AppendChild(day24Cell);

                    // Day 25 Cell
                    Cell day25Cell = CreateCell(GetShiftCodeByID((int)item.Day25), "AB" + index.ToString());
                    row.AppendChild(day25Cell);

                    // Day 26 Cell
                    Cell day26Cell = CreateCell(GetShiftCodeByID((int)item.Day26), "AC" + index.ToString());
                    row.AppendChild(day26Cell);

                    // Day 27 Cell
                    Cell day27Cell = CreateCell(GetShiftCodeByID((int)item.Day27), "AD" + index.ToString());
                    row.AppendChild(day27Cell);

                    // Day 28 Cell
                    Cell day28Cell = CreateCell(GetShiftCodeByID((int)item.Day28), "AE" + index.ToString());
                    row.AppendChild(day28Cell);

                    // Day 29 Cell
                    Cell day29Cell = CreateCell(GetShiftCodeByID((int)item.Day29), "AF" + index.ToString());
                    row.AppendChild(day29Cell);

                    // Day 30 Cell
                    Cell day30Cell = CreateCell(GetShiftCodeByID((int)item.Day30), "AG" + index.ToString());
                    row.AppendChild(day30Cell);

                    // Day 31 Cell
                    Cell day31Cell = CreateCell(GetShiftCodeByID((int)item.Day31), "AH" + index.ToString());
                    row.AppendChild(day31Cell);

                    // Append Row to SheetData
                    sheetData.AppendChild(row);

                    count++;
                    index += 2;
                }

                

                // save
                worksheetPart.Worksheet.Save();

            }
        }
        */

        public static string GetShiftCodeByID(int shiftcode)
        {
            string result = "N";

            int localCode = shiftcode;

            switch (localCode)
            {
                case (int)DTO.CaDTO.ShiftE.X:
                    result = "X"; break;
                case (int)DTO.CaDTO.ShiftE.S:
                    result = "S"; break;
                case (int)DTO.CaDTO.ShiftE.C:
                    result = "C"; break;
                case (int)DTO.CaDTO.ShiftE.H:
                    result = "H"; break;
                case (int)DTO.CaDTO.ShiftE.Tvs:
                    result = "VS"; break;
                case (int)DTO.CaDTO.ShiftE.Tvc:
                    result = "VC"; break;
                case (int)DTO.CaDTO.ShiftE.C12:
                    result = "V12"; break;
                case (int)DTO.CaDTO.ShiftE.Sa:
                    result = "S"; break;
                case (int)DTO.CaDTO.ShiftE.C1:
                    result = "C1"; break;
                case (int)DTO.CaDTO.ShiftE.C2:
                    result = "C2"; break;
                case (int)DTO.CaDTO.ShiftE.Sa2:
                    result = "S2"; break;
            }

            return result;
        }

        public static DTO.LichDTO Getallcanv(int Ngay, int thang, int nam, Int32 localID)
        {
            Int32 idUser = localID;
            DTO.LichDTO dsca = null;
            using (Moved.Moved_DB dc = new Moved_DB(_connString))
            {
                var objCanv = (from caNV in dc.AL_ScheduleDetail
                               join sc in dc.AL_UserSchedule on caNV.ScheduleID equals sc.ScheduleID
                               join ca in dc.AL_Shift on caNV.ShiftID equals ca.ShiftID
                               where sc.StartDate.Month == thang
                                       && sc.StartDate.Year == nam
                                       && caNV.EndDay == (Ngay - 1)
                                       && sc.UserID == idUser
                               select new
                               {
                                   tglam = caNV.StartTime,
                                   tgve = caNV.EndTime,
                                   ngaybd = caNV.StartDay,
                                   checkInFrom = ca.CheckInTimeFrom,
                                   checkInTo = ca.CheckInTimeTo,
                                   checkOutFrom = ca.CheckOutTimeFrom,
                                   checkOutTo = ca.CheckOutTimeTo,
                                   maca = ca.ShiftCode,
                                   ngaykt = caNV.EndDay,
                                   tennv = sc.UserID,
                                   malich = sc.ScheduleID

                               });
                foreach (var item in objCanv)
                {
                    dsca = new LichDTO();
                    dsca.UserID = item.tennv;
                    dsca.NgayBd = item.ngaybd;
                    dsca.Ngaykt = item.ngaykt;
                    dsca.ThoigianBd = item.tglam;
                    dsca.ThoigianKT = item.tgve;
                    dsca.TimeInFrom = (DateTime)item.checkInFrom;
                    dsca.TimeInTo = (DateTime)item.checkInTo;
                    dsca.TimeOutFrom = (DateTime)item.checkOutFrom;
                    dsca.TimeOutTo = (DateTime)item.checkOutTo;
                    dsca.TenLich = item.maca;
                    dsca.MaCTLich = item.malich;
                    break;

                }
                return dsca;
            }
        }

        public static void CreateScheduleByDepartAndDate(int departmentId, DateTime startTime, DateTime endTime)
        {
            int localDepart = departmentId;
            DateTime localStartTime = startTime;
            DateTime localEndTime = endTime;

            List<WIT_Schedule> listSchedule = new List<WIT_Schedule>();

            using (Moved_DB dc = new Moved_DB(_connString))
            {
                // Get list user in department
                var users = (from us in dc.AL_UserInfo
                             join de in dc.AL_Department on us.DepartmentID equals de.DepartmentID
                             where us.DepartmentID == localDepart
                             select us
                                 );

                // Insert new schedule for each item
                foreach (var user in users)
                {
                    var userSchedule = new WIT_Schedule
                    {
                        UserID = user.UserID,
                        StartTime = localStartTime,
                        EndTime = localEndTime,
                        Status = 1,

                        Day1 = (int)DTO.CaDTO.ShiftE.Default,
                        Day2 = (int)DTO.CaDTO.ShiftE.Default,
                        Day3 = (int)DTO.CaDTO.ShiftE.Default,
                        Day4 = (int)DTO.CaDTO.ShiftE.Default,
                        Day5 = (int)DTO.CaDTO.ShiftE.Default,
                        Day6 = (int)DTO.CaDTO.ShiftE.Default,
                        Day7 = (int)DTO.CaDTO.ShiftE.Default,
                        Day8 = (int)DTO.CaDTO.ShiftE.Default,
                        Day9 = (int)DTO.CaDTO.ShiftE.Default,
                        Day10 = (int)DTO.CaDTO.ShiftE.Default,

                        Day11 = (int)DTO.CaDTO.ShiftE.Default,
                        Day12 = (int)DTO.CaDTO.ShiftE.Default,
                        Day13 = (int)DTO.CaDTO.ShiftE.Default,
                        Day14 = (int)DTO.CaDTO.ShiftE.Default,
                        Day15 = (int)DTO.CaDTO.ShiftE.Default,
                        Day16 = (int)DTO.CaDTO.ShiftE.Default,
                        Day17 = (int)DTO.CaDTO.ShiftE.Default,
                        Day18 = (int)DTO.CaDTO.ShiftE.Default,
                        Day19 = (int)DTO.CaDTO.ShiftE.Default,
                        Day20 = (int)DTO.CaDTO.ShiftE.Default,

                        Day21 = (int)DTO.CaDTO.ShiftE.Default,
                        Day22 = (int)DTO.CaDTO.ShiftE.Default,
                        Day23 = (int)DTO.CaDTO.ShiftE.Default,
                        Day24 = (int)DTO.CaDTO.ShiftE.Default,
                        Day25 = (int)DTO.CaDTO.ShiftE.Default,
                        Day26 = (int)DTO.CaDTO.ShiftE.Default,
                        Day27 = (int)DTO.CaDTO.ShiftE.Default,
                        Day28 = (int)DTO.CaDTO.ShiftE.Default,
                        Day29 = (int)DTO.CaDTO.ShiftE.Default,
                        Day30 = (int)DTO.CaDTO.ShiftE.Default,
                        Day31 = (int)DTO.CaDTO.ShiftE.Default,

                    };

                    listSchedule.Add(userSchedule);
                }

                if (listSchedule.Count < 1)
                {
                    return;
                }

                dc.WIT_Schedule.InsertAllOnSubmit(listSchedule);

                dc.SubmitChanges();
            }
        }

        public static int UpdateScheduleByIDDayMonthYear(int userId, int day, int month, int year, int value)
        {
            int result = 0;

            int localUserID = userId;
            int localDay = day;
            int localMonth = month;
            int localYear = year;
            int localValue = value;

            using (Moved_DB dc = new Moved_DB(_connString))
            {
                var mySchedule = dc.WIT_Schedule.Single(item => item.UserID == localUserID && item.StartTime.Month == localMonth && item.StartTime.Year == year);

                switch (localDay)
                {
                    case 1:
                        mySchedule.Day1 = value; break;
                    case 2:
                        mySchedule.Day2 = value; break;
                    case 3:
                        mySchedule.Day3 = value; break;
                    case 4:
                        mySchedule.Day4 = value; break;
                    case 5:
                        mySchedule.Day5 = value; break;
                    case 6:
                        mySchedule.Day6 = value; break;
                    case 7:
                        mySchedule.Day7 = value; break;
                    case 8:
                        mySchedule.Day8 = value; break;
                    case 9:
                        mySchedule.Day9 = value; break;
                    case 10:
                        mySchedule.Day10 = value; break;
                    case 11:
                        mySchedule.Day11 = value; break;
                    case 12:
                        mySchedule.Day12 = value; break;
                    case 13:
                        mySchedule.Day13 = value; break;
                    case 14:
                        mySchedule.Day14 = value; break;
                    case 15:
                        mySchedule.Day15 = value; break;
                    case 16:
                        mySchedule.Day16 = value; break;
                    case 17:
                        mySchedule.Day17 = value; break;
                    case 18:
                        mySchedule.Day18 = value; break;
                    case 19:
                        mySchedule.Day19 = value; break;
                    case 20:
                        mySchedule.Day20 = value; break;
                    case 21:
                        mySchedule.Day21 = value; break;
                    case 22:
                        mySchedule.Day22 = value; break;
                    case 23:
                        mySchedule.Day23 = value; break;
                    case 24:
                        mySchedule.Day24 = value; break;
                    case 25:
                        mySchedule.Day25 = value; break;
                    case 26:
                        mySchedule.Day26 = value; break;
                    case 27:
                        mySchedule.Day27 = value; break;
                    case 28:
                        mySchedule.Day28 = value; break;
                    case 29:
                        mySchedule.Day29 = value; break;
                    case 30:
                        mySchedule.Day30 = value; break;
                    case 31:
                        mySchedule.Day31 = value; break;
                }

                dc.SubmitChanges();

            }

            return result;
        }

        public static string GetLabelLichByUserIdStartDayEndDay(int userid, int startday, int endday, DateTime starttime)
        {
            int localUserId = userid;
            int startDay = startday;
            int endDay = endday;
            DateTime localStartTime = starttime;
            string label = " ";

            using (Moved.Moved_DB dc = new Moved_DB(_connString))
            {
                var mySchedule = dc.WIT_Schedule.Single(item => item.UserID == localUserId);

            }

            return label;
        }

        public static void InsertEmpIntoDepSchedule(int departmentId, DateTime startTime, DateTime endTime)
        {
            int localDepartmentId = departmentId;
            DateTime localStartTime = startTime;
            DateTime localEndTime = endTime;
            List<WIT_Schedule> nvs = new List<WIT_Schedule>();
            using (Moved_DB dc = new Moved_DB(_connString))
            {
                var all_user = (from us in dc.AL_UserInfo
                                where us.DepartmentID == localDepartmentId
                                select new { us.UserID });
                var user_sc = (from sc in dc.WIT_Schedule
                               join us in dc.AL_UserInfo on sc.UserID equals us.UserID
                               where us.DepartmentID == localDepartmentId
                               && sc.StartTime.Month == startTime.Month
                               && sc.StartTime.Year == startTime.Year
                               && sc.EndTime.Month == endTime.Month
                               && sc.EndTime.Year == endTime.Year
                               select new { sc.UserID });
                var except = all_user.Except(user_sc);

                foreach (var n in except)
                {
                    WIT_Schedule nvm = new WIT_Schedule();
                    nvm.UserID = n.UserID;
                    nvm.StartTime = localStartTime;
                    nvm.EndTime = localEndTime;
                    nvm.Status = 1;
                    nvm.Day1 = (int)DTO.CaDTO.ShiftE.Default;
                    nvm.Day2 = (int)DTO.CaDTO.ShiftE.Default;
                    nvm.Day3 = (int)DTO.CaDTO.ShiftE.Default;
                    nvm.Day4 = (int)DTO.CaDTO.ShiftE.Default;
                    nvm.Day5 = (int)DTO.CaDTO.ShiftE.Default;
                    nvm.Day6 = (int)DTO.CaDTO.ShiftE.Default;
                    nvm.Day7 = (int)DTO.CaDTO.ShiftE.Default;
                    nvm.Day8 = (int)DTO.CaDTO.ShiftE.Default;
                    nvm.Day9 = (int)DTO.CaDTO.ShiftE.Default;
                    nvm.Day10 = (int)DTO.CaDTO.ShiftE.Default;
                    nvm.Day11 = (int)DTO.CaDTO.ShiftE.Default;
                    nvm.Day12 = (int)DTO.CaDTO.ShiftE.Default;
                    nvm.Day13 = (int)DTO.CaDTO.ShiftE.Default;
                    nvm.Day14 = (int)DTO.CaDTO.ShiftE.Default;
                    nvm.Day15 = (int)DTO.CaDTO.ShiftE.Default;
                    nvm.Day16 = (int)DTO.CaDTO.ShiftE.Default;
                    nvm.Day17 = (int)DTO.CaDTO.ShiftE.Default;
                    nvm.Day18 = (int)DTO.CaDTO.ShiftE.Default;
                    nvm.Day19 = (int)DTO.CaDTO.ShiftE.Default;
                    nvm.Day20 = (int)DTO.CaDTO.ShiftE.Default;
                    nvm.Day21 = (int)DTO.CaDTO.ShiftE.Default;
                    nvm.Day22 = (int)DTO.CaDTO.ShiftE.Default;
                    nvm.Day23 = (int)DTO.CaDTO.ShiftE.Default;
                    nvm.Day24 = (int)DTO.CaDTO.ShiftE.Default;
                    nvm.Day25 = (int)DTO.CaDTO.ShiftE.Default;
                    nvm.Day26 = (int)DTO.CaDTO.ShiftE.Default;
                    nvm.Day27 = (int)DTO.CaDTO.ShiftE.Default;
                    nvm.Day28 = (int)DTO.CaDTO.ShiftE.Default;
                    nvm.Day29 = (int)DTO.CaDTO.ShiftE.Default;
                    nvm.Day30 = (int)DTO.CaDTO.ShiftE.Default;
                    nvm.Day31 = (int)DTO.CaDTO.ShiftE.Default;

                    nvs.Add(nvm);
                }

                dc.WIT_Schedule.InsertAllOnSubmit(nvs);

                dc.SubmitChanges();
            }

        }


        public static List<DTO.WitSchedule> GetScheduleByDepartAndMonth(int departmentId, DateTime time)
        {
            int localDepartmentId = departmentId;
            DateTime localTime = time;

            List<DTO.WitSchedule> listSchedule = new List<WitSchedule>();

            using (Moved.Moved_DB dc = new Moved_DB(_connString))
            {
                var schedules = (from ws in dc.WIT_Schedule
                                 join ue in dc.AL_UserInfo on ws.UserID equals ue.UserID
                                 where ue.DepartmentID == localDepartmentId
                                            && ws.StartTime.Month == localTime.Month
                                            && ws.StartTime.Year == localTime.Year
                                            && ws.EndTime.Month == localTime.Month
                                            && ws.EndTime.Year == localTime.Year
                                 select new
                                {
                                    scheduleID = ws.ScheduleID,
                                    userID = ws.UserID,
                                    userName = ue.Name,
                                    startTime = ws.StartTime,
                                    endTime = ws.EndTime,
                                    status = ws.Status,
                                    day1 = ws.Day1,
                                    day2 = ws.Day2,
                                    day3 = ws.Day3,
                                    day4 = ws.Day4,
                                    day5 = ws.Day5,
                                    day6 = ws.Day6,
                                    day7 = ws.Day7,
                                    day8 = ws.Day8,
                                    day9 = ws.Day9,
                                    day10 = ws.Day10,
                                    day11 = ws.Day11,
                                    day12 = ws.Day12,
                                    day13 = ws.Day13,
                                    day14 = ws.Day14,
                                    day15 = ws.Day15,
                                    day16 = ws.Day16,
                                    day17 = ws.Day17,
                                    day18 = ws.Day18,
                                    day19 = ws.Day19,
                                    day20 = ws.Day20,
                                    day21 = ws.Day21,
                                    day22 = ws.Day22,
                                    day23 = ws.Day23,
                                    day24 = ws.Day24,
                                    day25 = ws.Day25,
                                    day26 = ws.Day26,
                                    day27 = ws.Day27,
                                    day28 = ws.Day28,
                                    day29 = ws.Day29,
                                    day30 = ws.Day30,
                                    day31 = ws.Day31
                                }
                                        ).Distinct();

                foreach (var item in schedules)
                {
                    DTO.WitSchedule objSchedule = new WitSchedule();
                    objSchedule.ScheduleId = item.scheduleID;
                    objSchedule.UserId = item.userID;
                    objSchedule.UserName = item.userName;
                    objSchedule.StartTime = item.startTime;
                    objSchedule.EndTime = item.endTime;
                    objSchedule.Day1 = (int)item.day1;
                    objSchedule.Day2 = (int)item.day2;
                    objSchedule.Day3 = (int)item.day3;
                    objSchedule.Day4 = (int)item.day4;
                    objSchedule.Day5 = (int)item.day5;
                    objSchedule.Day6 = (int)item.day6;
                    objSchedule.Day7 = (int)item.day7;
                    objSchedule.Day8 = (int)item.day8;
                    objSchedule.Day9 = (int)item.day9;
                    objSchedule.Day10 = (int)item.day10;
                    objSchedule.Day11 = (int)item.day11;
                    objSchedule.Day12 = (int)item.day12;
                    objSchedule.Day13 = (int)item.day13;
                    objSchedule.Day14 = (int)item.day14;
                    objSchedule.Day15 = (int)item.day15;
                    objSchedule.Day16 = (int)item.day16;
                    objSchedule.Day17 = (int)item.day17;
                    objSchedule.Day18 = (int)item.day18;
                    objSchedule.Day19 = (int)item.day19;
                    objSchedule.Day20 = (int)item.day20;
                    objSchedule.Day21 = (int)item.day21;
                    objSchedule.Day22 = (int)item.day22;
                    objSchedule.Day23 = (int)item.day23;
                    objSchedule.Day24 = (int)item.day24;
                    objSchedule.Day25 = (int)item.day25;
                    objSchedule.Day26 = (int)item.day26;
                    objSchedule.Day27 = (int)item.day27;
                    objSchedule.Day28 = (int)item.day28;
                    objSchedule.Day29 = (int)item.day29;
                    objSchedule.Day30 = (int)item.day30;
                    objSchedule.Day31 = (int)item.day31;
                    listSchedule.Add(objSchedule);
                }
            }

            return listSchedule;
        }

        public static DTO.WitSchedule getScheduleByUserID(Int32 UserID, Int32 month)
        {
            Int32 localUserID = UserID;
            Int32 localMonth = month;
            DTO.WitSchedule objSchedule = null;
            using (Moved_DB dc = new Moved_DB(_connString))
            {
                var schedules = (from ws in dc.WIT_Schedule
                                 join us in dc.AL_UserInfo on ws.UserID equals us.UserID
                                 where ws.UserID == localUserID
                                    && ws.StartTime.Month == month
                                 select new
                                 {
                                     scheduleID = ws.ScheduleID,
                                     userID = ws.UserID,
                                     userName = us.FullName,
                                     startTime = ws.StartTime,
                                     endTime = ws.EndTime,
                                     status = ws.Status,
                                     day1 = ws.Day1,
                                     day2 = ws.Day2,
                                     day3 = ws.Day3,
                                     day4 = ws.Day4,
                                     day5 = ws.Day5,
                                     day6 = ws.Day6,
                                     day7 = ws.Day7,
                                     day8 = ws.Day8,
                                     day9 = ws.Day9,
                                     day10 = ws.Day10,
                                     day11 = ws.Day11,
                                     day12 = ws.Day12,
                                     day13 = ws.Day13,
                                     day14 = ws.Day14,
                                     day15 = ws.Day15,
                                     day16 = ws.Day16,
                                     day17 = ws.Day17,
                                     day18 = ws.Day18,
                                     day19 = ws.Day19,
                                     day20 = ws.Day20,
                                     day21 = ws.Day21,
                                     day22 = ws.Day22,
                                     day23 = ws.Day23,
                                     day24 = ws.Day24,
                                     day25 = ws.Day25,
                                     day26 = ws.Day26,
                                     day27 = ws.Day27,
                                     day28 = ws.Day28,
                                     day29 = ws.Day29,
                                     day30 = ws.Day30,
                                     day31 = ws.Day31
                                 }
                               ).Distinct();
                foreach (var item in schedules)
                {
                    objSchedule = new WitSchedule();
                    objSchedule.ScheduleId = item.scheduleID;
                    objSchedule.UserId = item.userID;
                    objSchedule.UserName = item.userName;
                    objSchedule.StartTime = item.startTime;
                    objSchedule.EndTime = item.endTime;
                    objSchedule.Day1 = (int)item.day1;
                    objSchedule.Day2 = (int)item.day2;
                    objSchedule.Day3 = (int)item.day3;
                    objSchedule.Day4 = (int)item.day4;
                    objSchedule.Day5 = (int)item.day5;
                    objSchedule.Day6 = (int)item.day6;
                    objSchedule.Day7 = (int)item.day7;
                    objSchedule.Day8 = (int)item.day8;
                    objSchedule.Day9 = (int)item.day9;
                    objSchedule.Day10 = (int)item.day10;
                    objSchedule.Day11 = (int)item.day11;
                    objSchedule.Day12 = (int)item.day12;
                    objSchedule.Day13 = (int)item.day13;
                    objSchedule.Day14 = (int)item.day14;
                    objSchedule.Day15 = (int)item.day15;
                    objSchedule.Day16 = (int)item.day16;
                    objSchedule.Day17 = (int)item.day17;
                    objSchedule.Day18 = (int)item.day18;
                    objSchedule.Day19 = (int)item.day19;
                    objSchedule.Day20 = (int)item.day20;
                    objSchedule.Day21 = (int)item.day21;
                    objSchedule.Day22 = (int)item.day22;
                    objSchedule.Day23 = (int)item.day23;
                    objSchedule.Day24 = (int)item.day24;
                    objSchedule.Day25 = (int)item.day25;
                    objSchedule.Day26 = (int)item.day26;
                    objSchedule.Day27 = (int)item.day27;
                    objSchedule.Day28 = (int)item.day28;
                    objSchedule.Day29 = (int)item.day29;
                    objSchedule.Day30 = (int)item.day30;
                    objSchedule.Day31 = (int)item.day31;
                }
                return objSchedule;
            }


        }


        public static DTO.LichDTO GetTime(Int32 ShiftID)
        {
            Int32 localShift = ShiftID;
            DTO.LichDTO dsca = null;
            using (Moved.Moved_DB dc = new Moved_DB(_connString))
            {
                var objCanv = (from ca in dc.AL_Shift
                               where ca.ShiftID == localShift
                               select new
                               {
                                   checkInFrom = ca.CheckInTimeFrom,
                                   checkInTo = ca.CheckInTimeTo,
                                   checkOutFrom = ca.CheckOutTimeFrom,
                                   StartTime = ca.StartTime,
                                   EndTime = ca.EndTime,
                                   TimeWork = ca.WorkMinutes,
                                   checkOutTo = ca.CheckOutTimeTo,
                                   maca = ca.ShiftCode
                               });
                foreach (var item in objCanv)
                {
                    dsca = new LichDTO();
                    dsca.TimeInFrom = (DateTime)item.checkInFrom;
                    dsca.TimeInTo = (DateTime)item.checkInTo;
                    dsca.TimeOutFrom = (DateTime)item.checkOutFrom;
                    dsca.ThoigianBd = item.StartTime;
                    dsca.TimeWork = (int)item.TimeWork;
                    dsca.ThoigianKT = item.EndTime;
                    dsca.TimeOutTo = (DateTime)item.checkOutTo;
                    dsca.TenLich = item.maca;
                    break;

                }
                return dsca;
            }
        }

        public static string GetHday1(int UserID, int Day)
        {
            string result = "";
            int localID = UserID;
            int localday = Day;
            using (Moved.Moved_DB gh = new Moved_DB(_connString))
            {
                var hday = (from hd in gh.WIT_Schedule
                                where hd.UserID==localID
                                select hd);
                foreach (var gd in hday)
                {
                    switch (localday)
                    {
                        case 1: if (gd.HDay1 == null) result = "8:00"; else { string[] shd = gd.HDay1.Split(' '); result = shd[0]; }; break;
                        case 2: if (gd.HDay2 == null) result = "8:00"; else { string[] shd = gd.HDay2.Split(' '); result = shd[0]; }; break;
                        case 3: if (gd.HDay3 == null) result = "8:00"; else { string[] shd = gd.HDay3.Split(' '); result = shd[0]; }; break;
                        case 4: if (gd.HDay4 == null) result = "8:00"; else { string[] shd = gd.HDay4.Split(' '); result = shd[0]; }; break;
                        case 5: if (gd.HDay5 == null) result = "8:00"; else { string[] shd = gd.HDay5.Split(' '); result = shd[0]; }; break;
                        case 6: if (gd.HDay6 == null) result = "8:00"; else { string[] shd = gd.HDay6.Split(' '); result = shd[0]; }; break;
                        case 7: if (gd.HDay7 == null) result = "8:00"; else { string[] shd = gd.HDay7.Split(' '); result = shd[0]; }; break;
                        case 8: if (gd.HDay8 == null) result = "8:00"; else { string[] shd = gd.HDay8.Split(' '); result = shd[0]; }; break;
                        case 9: if (gd.HDay9 == null) result = "8:00"; else { string[] shd = gd.HDay9.Split(' '); result = shd[0]; }; break;
                        case 10: if (gd.HDay10 == null) result = "8:00"; else { string[] shd = gd.HDay10.Split(' '); result = shd[0]; }; break;
                        case 11: if (gd.HDay11 == null) result = "8:00"; else { string[] shd = gd.HDay11.Split(' '); result = shd[0]; }; break;
                        case 12: if (gd.HDay12 == null) result = "8:00"; else { string[] shd = gd.HDay12.Split(' '); result = shd[0]; }; break;
                        case 13: if (gd.HDay13 == null) result = "8:00"; else { string[] shd = gd.HDay13.Split(' '); result = shd[0]; }; break;
                        case 14: if (gd.HDay14 == null) result = "8:00"; else { string[] shd = gd.HDay14.Split(' '); result = shd[0]; }; break;
                        case 15: if (gd.HDay15 == null) result = "8:00"; else { string[] shd = gd.HDay15.Split(' '); result = shd[0]; }; break;
                        case 16: if (gd.HDay16 == null) result = "8:00"; else { string[] shd = gd.HDay16.Split(' '); result = shd[0]; }; break;
                        case 17: if (gd.HDay17 == null) result = "8:00"; else { string[] shd = gd.HDay17.Split(' '); result = shd[0]; }; break;
                        case 18: if (gd.HDay18 == null) result = "8:00"; else { string[] shd = gd.HDay18.Split(' '); result = shd[0]; }; break;
                        case 19: if (gd.HDay19 == null) result = "8:00"; else { string[] shd = gd.HDay19.Split(' '); result = shd[0]; }; break;
                        case 20: if (gd.HDay20 == null) result = "8:00"; else { string[] shd = gd.HDay20.Split(' '); result = shd[0]; }; break;
                        case 21: if (gd.HDay21 == null) result = "8:00"; else { string[] shd = gd.HDay21.Split(' '); result = shd[0]; }; break;
                        case 22: if (gd.HDay22 == null) result = "8:00"; else { string[] shd = gd.HDay22.Split(' '); result = shd[0]; }; break;
                        case 23: if (gd.HDay23 == null) result = "8:00"; else { string[] shd = gd.HDay23.Split(' '); result = shd[0]; }; break;
                        case 24: if (gd.HDay24 == null) result = "8:00"; else { string[] shd = gd.HDay24.Split(' '); result = shd[0]; }; break;
                        case 25: if (gd.HDay25 == null) result = "8:00"; else { string[] shd = gd.HDay25.Split(' '); result = shd[0]; }; break;
                        case 26: if (gd.HDay26 == null) result = "8:00"; else { string[] shd = gd.HDay26.Split(' '); result = shd[0]; }; break;
                        case 27: if (gd.HDay27 == null) result = "8:00"; else { string[] shd = gd.HDay27.Split(' '); result = shd[0]; }; break;
                        case 28: if (gd.HDay28 == null) result = "8:00"; else { string[] shd = gd.HDay28.Split(' '); result = shd[0]; }; break;
                        case 29: if (gd.HDay29 == null) result = "8:00"; else { string[] shd = gd.HDay29.Split(' '); result = shd[0]; }; break;
                        case 30: if (gd.HDay30 == null) result = "8:00"; else { string[] shd = gd.HDay30.Split(' '); result = shd[0]; }; break;
                        case 31: if (gd.HDay31 == null) result = "8:00"; else { string[] shd = gd.HDay31.Split(' '); result = shd[0]; }; break;
                    }
                }
                return result;
                }
            }
        public static string GetHday2(int UserID, int Day)
        {
            string result = "";
            int localID = UserID;
            int localday = Day;
            using (Moved.Moved_DB gh = new Moved_DB(_connString))
            {
                var hday = (from hd in gh.WIT_Schedule
                            where hd.UserID == localID
                            select hd);
                foreach (var gd in hday)
                {
                    switch (localday)
                    {
                        case 1: if (gd.HDay1 == null) result = "17:00"; else { string[] shd = gd.HDay1.Split(' '); result = shd[1]; }; break;
                        case 2: if (gd.HDay2 == null) result = "17:00"; else { string[] shd = gd.HDay2.Split(' '); result = shd[1]; }; break;
                        case 3: if (gd.HDay3 == null) result = "17:00"; else { string[] shd = gd.HDay3.Split(' '); result = shd[1]; }; break;
                        case 4: if (gd.HDay4 == null) result = "17:00"; else { string[] shd = gd.HDay4.Split(' '); result = shd[1]; }; break;
                        case 5: if (gd.HDay5 == null) result = "17:00"; else { string[] shd = gd.HDay5.Split(' '); result = shd[1]; }; break;
                        case 6: if (gd.HDay6 == null) result = "17:00"; else { string[] shd = gd.HDay6.Split(' '); result = shd[1]; }; break;
                        case 7: if (gd.HDay7 == null) result = "17:00"; else { string[] shd = gd.HDay7.Split(' '); result = shd[1]; }; break;
                        case 8: if (gd.HDay8 == null) result = "17:00"; else { string[] shd = gd.HDay8.Split(' '); result = shd[1]; }; break;
                        case 9: if (gd.HDay9 == null) result = "17:00"; else { string[] shd = gd.HDay9.Split(' '); result = shd[1]; }; ; break;
                        case 10: if (gd.HDay10 == null) result = "17:00"; else { string[] shd = gd.HDay10.Split(' '); result = shd[1]; }; break;
                        case 11: if (gd.HDay11 == null) result = "17:00"; else { string[] shd = gd.HDay11.Split(' '); result = shd[1]; }; break;
                        case 12: if (gd.HDay12 == null) result = "17:00"; else { string[] shd = gd.HDay12.Split(' '); result = shd[1]; }; break;
                        case 13: if (gd.HDay13 == null) result = "17:00"; else { string[] shd = gd.HDay13.Split(' '); result = shd[1]; }; break;
                        case 14: if (gd.HDay14 == null) result = "17:00"; else { string[] shd = gd.HDay14.Split(' '); result = shd[1]; }; break;
                        case 15: if (gd.HDay15 == null) result = "17:00"; else { string[] shd = gd.HDay15.Split(' '); result = shd[1]; }; break;
                        case 16: if (gd.HDay16 == null) result = "17:00"; else { string[] shd = gd.HDay16.Split(' '); result = shd[1]; }; break;
                        case 17: if (gd.HDay17 == null) result = "17:00"; else { string[] shd = gd.HDay17.Split(' '); result = shd[1]; }; break;
                        case 18: if (gd.HDay18 == null) result = "17:00"; else { string[] shd = gd.HDay18.Split(' '); result = shd[1]; }; break;
                        case 19: if (gd.HDay19 == null) result = "17:00"; else { string[] shd = gd.HDay19.Split(' '); result = shd[1]; }; break;
                        case 20: if (gd.HDay20 == null) result = "17:00"; else { string[] shd = gd.HDay20.Split(' '); result = shd[1]; }; break;
                        case 21: if (gd.HDay21 == null) result = "17:00"; else { string[] shd = gd.HDay21.Split(' '); result = shd[1]; }; break;
                        case 22: if (gd.HDay22 == null) result = "17:00"; else { string[] shd = gd.HDay22.Split(' '); result = shd[1]; }; break;
                        case 23: if (gd.HDay23 == null) result = "17:00"; else { string[] shd = gd.HDay23.Split(' '); result = shd[1]; }; break;
                        case 24: if (gd.HDay24 == null) result = "17:00"; else { string[] shd = gd.HDay24.Split(' '); result = shd[1]; }; break;
                        case 25: if (gd.HDay25 == null) result = "17:00"; else { string[] shd = gd.HDay25.Split(' '); result = shd[1]; }; break;
                        case 26: if (gd.HDay26 == null) result = "17:00"; else { string[] shd = gd.HDay26.Split(' '); result = shd[1]; }; break;
                        case 27: if (gd.HDay27 == null) result = "17:00"; else { string[] shd = gd.HDay27.Split(' '); result = shd[1]; }; break;
                        case 28: if (gd.HDay28 == null) result = "17:00"; else { string[] shd = gd.HDay28.Split(' '); result = shd[1]; };  break;
                        case 29: if (gd.HDay29 == null) result = "17:00"; else { string[] shd = gd.HDay29.Split(' '); result = shd[1]; }; break;
                        case 30: if (gd.HDay30 == null) result = "17:00"; else { string[] shd = gd.HDay30.Split(' '); result = shd[1]; }; break;
                        case 31: if (gd.HDay31 == null) result = "17:00"; else { string[] shd = gd.HDay31.Split(' '); result = shd[1]; }; break;
                    }
                }
                return result;
            }
        }
        }

    }

